1"""
   2## Expressions
   3
   4Every AST node in SQLGlot is represented by a subclass of `Expression`.
   5
   6This module contains the implementation of all supported `Expression` types. Additionally,
   7it exposes a number of helper functions, which are mainly used to programmatically build
   8SQL expressions, such as `sqlglot.expressions.select`.
   9
  10----
  11"""
  12
  13from __future__ import annotations
  14
  15import datetime
  16import math
  17import numbers
  18import re
  19import textwrap
  20import typing as t
  21from collections import deque
  22from copy import deepcopy
  23from enum import auto
  24from functools import reduce
  25
  26from sqlglot.errors import ErrorLevel, ParseError
  27from sqlglot.helper import (
  28    AutoName,
  29    camel_to_snake_case,
  30    ensure_collection,
  31    ensure_list,
  32    is_int,
  33    seq_get,
  34    subclasses,
  35)
  36from sqlglot.tokens import Token
  37
  38if t.TYPE_CHECKING:
  39    from sqlglot._typing import E, Lit
  40    from sqlglot.dialects.dialect import DialectType
  41
  42    Q = t.TypeVar("Q", bound="Query")
  43
  44
  45class _Expression(type):
  46    def __new__(cls, clsname, bases, attrs):
  47        klass = super().__new__(cls, clsname, bases, attrs)
  48
  49        # When an Expression class is created, its key is automatically set to be
  50        # the lowercase version of the class' name.
  51        klass.key = clsname.lower()
  52
  53        # This is so that docstrings are not inherited in pdoc
  54        klass.__doc__ = klass.__doc__ or ""
  55
  56        return klass
  57
  58
  59SQLGLOT_META = "sqlglot.meta"
  60TABLE_PARTS = ("this", "db", "catalog")
  61COLUMN_PARTS = ("this", "table", "db", "catalog")
  62
  63
  64class Expression(metaclass=_Expression):
  65    """
  66    The base class for all expressions in a syntax tree. Each Expression encapsulates any necessary
  67    context, such as its child expressions, their names (arg keys), and whether a given child expression
  68    is optional or not.
  69
  70    Attributes:
  71        key: a unique key for each class in the Expression hierarchy. This is useful for hashing
  72            and representing expressions as strings.
  73        arg_types: determines the arguments (child nodes) supported by an expression. It maps
  74            arg keys to booleans that indicate whether the corresponding args are optional.
  75        parent: a reference to the parent expression (or None, in case of root expressions).
  76        arg_key: the arg key an expression is associated with, i.e. the name its parent expression
  77            uses to refer to it.
  78        index: the index of an expression if it is inside of a list argument in its parent.
  79        comments: a list of comments that are associated with a given expression. This is used in
  80            order to preserve comments when transpiling SQL code.
  81        type: the `sqlglot.expressions.DataType` type of an expression. This is inferred by the
  82            optimizer, in order to enable some transformations that require type information.
  83        meta: a dictionary that can be used to store useful metadata for a given expression.
  84
  85    Example:
  86        >>> class Foo(Expression):
  87        ...     arg_types = {"this": True, "expression": False}
  88
  89        The above definition informs us that Foo is an Expression that requires an argument called
  90        "this" and may also optionally receive an argument called "expression".
  91
  92    Args:
  93        args: a mapping used for retrieving the arguments of an expression, given their arg keys.
  94    """
  95
  96    key = "expression"
  97    arg_types = {"this": True}
  98    __slots__ = ("args", "parent", "arg_key", "index", "comments", "_type", "_meta", "_hash")
  99
 100    def __init__(self, **args: t.Any):
 101        self.args: t.Dict[str, t.Any] = args
 102        self.parent: t.Optional[Expression] = None
 103        self.arg_key: t.Optional[str] = None
 104        self.index: t.Optional[int] = None
 105        self.comments: t.Optional[t.List[str]] = None
 106        self._type: t.Optional[DataType] = None
 107        self._meta: t.Optional[t.Dict[str, t.Any]] = None
 108        self._hash: t.Optional[int] = None
 109
 110        for arg_key, value in self.args.items():
 111            self._set_parent(arg_key, value)
 112
 113    def __eq__(self, other) -> bool:
 114        return type(self) is type(other) and hash(self) == hash(other)
 115
 116    @property
 117    def hashable_args(self) -> t.Any:
 118        return frozenset(
 119            (k, tuple(_norm_arg(a) for a in v) if type(v) is list else _norm_arg(v))
 120            for k, v in self.args.items()
 121            if not (v is None or v is False or (type(v) is list and not v))
 122        )
 123
 124    def __hash__(self) -> int:
 125        if self._hash is not None:
 126            return self._hash
 127
 128        return hash((self.__class__, self.hashable_args))
 129
 130    @property
 131    def this(self) -> t.Any:
 132        """
 133        Retrieves the argument with key "this".
 134        """
 135        return self.args.get("this")
 136
 137    @property
 138    def expression(self) -> t.Any:
 139        """
 140        Retrieves the argument with key "expression".
 141        """
 142        return self.args.get("expression")
 143
 144    @property
 145    def expressions(self) -> t.List[t.Any]:
 146        """
 147        Retrieves the argument with key "expressions".
 148        """
 149        return self.args.get("expressions") or []
 150
 151    def text(self, key) -> str:
 152        """
 153        Returns a textual representation of the argument corresponding to "key". This can only be used
 154        for args that are strings or leaf Expression instances, such as identifiers and literals.
 155        """
 156        field = self.args.get(key)
 157        if isinstance(field, str):
 158            return field
 159        if isinstance(field, (Identifier, Literal, Var)):
 160            return field.this
 161        if isinstance(field, (Star, Null)):
 162            return field.name
 163        return ""
 164
 165    @property
 166    def is_string(self) -> bool:
 167        """
 168        Checks whether a Literal expression is a string.
 169        """
 170        return isinstance(self, Literal) and self.args["is_string"]
 171
 172    @property
 173    def is_number(self) -> bool:
 174        """
 175        Checks whether a Literal expression is a number.
 176        """
 177        return isinstance(self, Literal) and not self.args["is_string"]
 178
 179    @property
 180    def is_negative(self) -> bool:
 181        """
 182        Checks whether an expression is negative.
 183
 184        Handles both exp.Neg and Literal numbers with "-" which come from optimizer.simplify.
 185        """
 186        return isinstance(self, Neg) or (self.is_number and self.this.startswith("-"))
 187
 188    @property
 189    def is_int(self) -> bool:
 190        """
 191        Checks whether a Literal expression is an integer.
 192        """
 193        return self.is_number and is_int(self.name)
 194
 195    @property
 196    def is_star(self) -> bool:
 197        """Checks whether an expression is a star."""
 198        return isinstance(self, Star) or (isinstance(self, Column) and isinstance(self.this, Star))
 199
 200    @property
 201    def alias(self) -> str:
 202        """
 203        Returns the alias of the expression, or an empty string if it's not aliased.
 204        """
 205        if isinstance(self.args.get("alias"), TableAlias):
 206            return self.args["alias"].name
 207        return self.text("alias")
 208
 209    @property
 210    def alias_column_names(self) -> t.List[str]:
 211        table_alias = self.args.get("alias")
 212        if not table_alias:
 213            return []
 214        return [c.name for c in table_alias.args.get("columns") or []]
 215
 216    @property
 217    def name(self) -> str:
 218        return self.text("this")
 219
 220    @property
 221    def alias_or_name(self) -> str:
 222        return self.alias or self.name
 223
 224    @property
 225    def output_name(self) -> str:
 226        """
 227        Name of the output column if this expression is a selection.
 228
 229        If the Expression has no output name, an empty string is returned.
 230
 231        Example:
 232            >>> from sqlglot import parse_one
 233            >>> parse_one("SELECT a").expressions[0].output_name
 234            'a'
 235            >>> parse_one("SELECT b AS c").expressions[0].output_name
 236            'c'
 237            >>> parse_one("SELECT 1 + 2").expressions[0].output_name
 238            ''
 239        """
 240        return ""
 241
 242    @property
 243    def type(self) -> t.Optional[DataType]:
 244        return self._type
 245
 246    @type.setter
 247    def type(self, dtype: t.Optional[DataType | DataType.Type | str]) -> None:
 248        if dtype and not isinstance(dtype, DataType):
 249            dtype = DataType.build(dtype)
 250        self._type = dtype  # type: ignore
 251
 252    def is_type(self, *dtypes) -> bool:
 253        return self.type is not None and self.type.is_type(*dtypes)
 254
 255    def is_leaf(self) -> bool:
 256        return not any(isinstance(v, (Expression, list)) for v in self.args.values())
 257
 258    @property
 259    def meta(self) -> t.Dict[str, t.Any]:
 260        if self._meta is None:
 261            self._meta = {}
 262        return self._meta
 263
 264    def __deepcopy__(self, memo):
 265        root = self.__class__()
 266        stack = [(self, root)]
 267
 268        while stack:
 269            node, copy = stack.pop()
 270
 271            if node.comments is not None:
 272                copy.comments = deepcopy(node.comments)
 273            if node._type is not None:
 274                copy._type = deepcopy(node._type)
 275            if node._meta is not None:
 276                copy._meta = deepcopy(node._meta)
 277            if node._hash is not None:
 278                copy._hash = node._hash
 279
 280            for k, vs in node.args.items():
 281                if hasattr(vs, "parent"):
 282                    stack.append((vs, vs.__class__()))
 283                    copy.set(k, stack[-1][-1])
 284                elif type(vs) is list:
 285                    copy.args[k] = []
 286
 287                    for v in vs:
 288                        if hasattr(v, "parent"):
 289                            stack.append((v, v.__class__()))
 290                            copy.append(k, stack[-1][-1])
 291                        else:
 292                            copy.append(k, v)
 293                else:
 294                    copy.args[k] = vs
 295
 296        return root
 297
 298    def copy(self):
 299        """
 300        Returns a deep copy of the expression.
 301        """
 302        return deepcopy(self)
 303
 304    def add_comments(self, comments: t.Optional[t.List[str]] = None) -> None:
 305        if self.comments is None:
 306            self.comments = []
 307
 308        if comments:
 309            for comment in comments:
 310                _, *meta = comment.split(SQLGLOT_META)
 311                if meta:
 312                    for kv in "".join(meta).split(","):
 313                        k, *v = kv.split("=")
 314                        value = v[0].strip() if v else True
 315                        self.meta[k.strip()] = value
 316                self.comments.append(comment)
 317
 318    def pop_comments(self) -> t.List[str]:
 319        comments = self.comments or []
 320        self.comments = None
 321        return comments
 322
 323    def append(self, arg_key: str, value: t.Any) -> None:
 324        """
 325        Appends value to arg_key if it's a list or sets it as a new list.
 326
 327        Args:
 328            arg_key (str): name of the list expression arg
 329            value (Any): value to append to the list
 330        """
 331        if type(self.args.get(arg_key)) is not list:
 332            self.args[arg_key] = []
 333        self._set_parent(arg_key, value)
 334        values = self.args[arg_key]
 335        if hasattr(value, "parent"):
 336            value.index = len(values)
 337        values.append(value)
 338
 339    def set(self, arg_key: str, value: t.Any, index: t.Optional[int] = None) -> None:
 340        """
 341        Sets arg_key to value.
 342
 343        Args:
 344            arg_key: name of the expression arg.
 345            value: value to set the arg to.
 346            index: if the arg is a list, this specifies what position to add the value in it.
 347        """
 348        if index is not None:
 349            expressions = self.args.get(arg_key) or []
 350
 351            if seq_get(expressions, index) is None:
 352                return
 353            if value is None:
 354                expressions.pop(index)
 355                for v in expressions[index:]:
 356                    v.index = v.index - 1
 357                return
 358
 359            if isinstance(value, list):
 360                expressions.pop(index)
 361                expressions[index:index] = value
 362            else:
 363                expressions[index] = value
 364
 365            value = expressions
 366        elif value is None:
 367            self.args.pop(arg_key, None)
 368            return
 369
 370        self.args[arg_key] = value
 371        self._set_parent(arg_key, value, index)
 372
 373    def _set_parent(self, arg_key: str, value: t.Any, index: t.Optional[int] = None) -> None:
 374        if hasattr(value, "parent"):
 375            value.parent = self
 376            value.arg_key = arg_key
 377            value.index = index
 378        elif type(value) is list:
 379            for index, v in enumerate(value):
 380                if hasattr(v, "parent"):
 381                    v.parent = self
 382                    v.arg_key = arg_key
 383                    v.index = index
 384
 385    @property
 386    def depth(self) -> int:
 387        """
 388        Returns the depth of this tree.
 389        """
 390        if self.parent:
 391            return self.parent.depth + 1
 392        return 0
 393
 394    def iter_expressions(self, reverse: bool = False) -> t.Iterator[Expression]:
 395        """Yields the key and expression for all arguments, exploding list args."""
 396        # remove tuple when python 3.7 is deprecated
 397        for vs in reversed(tuple(self.args.values())) if reverse else self.args.values():
 398            if type(vs) is list:
 399                for v in reversed(vs) if reverse else vs:
 400                    if hasattr(v, "parent"):
 401                        yield v
 402            else:
 403                if hasattr(vs, "parent"):
 404                    yield vs
 405
 406    def find(self, *expression_types: t.Type[E], bfs: bool = True) -> t.Optional[E]:
 407        """
 408        Returns the first node in this tree which matches at least one of
 409        the specified types.
 410
 411        Args:
 412            expression_types: the expression type(s) to match.
 413            bfs: whether to search the AST using the BFS algorithm (DFS is used if false).
 414
 415        Returns:
 416            The node which matches the criteria or None if no such node was found.
 417        """
 418        return next(self.find_all(*expression_types, bfs=bfs), None)
 419
 420    def find_all(self, *expression_types: t.Type[E], bfs: bool = True) -> t.Iterator[E]:
 421        """
 422        Returns a generator object which visits all nodes in this tree and only
 423        yields those that match at least one of the specified expression types.
 424
 425        Args:
 426            expression_types: the expression type(s) to match.
 427            bfs: whether to search the AST using the BFS algorithm (DFS is used if false).
 428
 429        Returns:
 430            The generator object.
 431        """
 432        for expression in self.walk(bfs=bfs):
 433            if isinstance(expression, expression_types):
 434                yield expression
 435
 436    def find_ancestor(self, *expression_types: t.Type[E]) -> t.Optional[E]:
 437        """
 438        Returns a nearest parent matching expression_types.
 439
 440        Args:
 441            expression_types: the expression type(s) to match.
 442
 443        Returns:
 444            The parent node.
 445        """
 446        ancestor = self.parent
 447        while ancestor and not isinstance(ancestor, expression_types):
 448            ancestor = ancestor.parent
 449        return ancestor  # type: ignore
 450
 451    @property
 452    def parent_select(self) -> t.Optional[Select]:
 453        """
 454        Returns the parent select statement.
 455        """
 456        return self.find_ancestor(Select)
 457
 458    @property
 459    def same_parent(self) -> bool:
 460        """Returns if the parent is the same class as itself."""
 461        return type(self.parent) is self.__class__
 462
 463    def root(self) -> Expression:
 464        """
 465        Returns the root expression of this tree.
 466        """
 467        expression = self
 468        while expression.parent:
 469            expression = expression.parent
 470        return expression
 471
 472    def walk(
 473        self, bfs: bool = True, prune: t.Optional[t.Callable[[Expression], bool]] = None
 474    ) -> t.Iterator[Expression]:
 475        """
 476        Returns a generator object which visits all nodes in this tree.
 477
 478        Args:
 479            bfs: if set to True the BFS traversal order will be applied,
 480                otherwise the DFS traversal will be used instead.
 481            prune: callable that returns True if the generator should stop traversing
 482                this branch of the tree.
 483
 484        Returns:
 485            the generator object.
 486        """
 487        if bfs:
 488            yield from self.bfs(prune=prune)
 489        else:
 490            yield from self.dfs(prune=prune)
 491
 492    def dfs(
 493        self, prune: t.Optional[t.Callable[[Expression], bool]] = None
 494    ) -> t.Iterator[Expression]:
 495        """
 496        Returns a generator object which visits all nodes in this tree in
 497        the DFS (Depth-first) order.
 498
 499        Returns:
 500            The generator object.
 501        """
 502        stack = [self]
 503
 504        while stack:
 505            node = stack.pop()
 506
 507            yield node
 508
 509            if prune and prune(node):
 510                continue
 511
 512            for v in node.iter_expressions(reverse=True):
 513                stack.append(v)
 514
 515    def bfs(
 516        self, prune: t.Optional[t.Callable[[Expression], bool]] = None
 517    ) -> t.Iterator[Expression]:
 518        """
 519        Returns a generator object which visits all nodes in this tree in
 520        the BFS (Breadth-first) order.
 521
 522        Returns:
 523            The generator object.
 524        """
 525        queue = deque([self])
 526
 527        while queue:
 528            node = queue.popleft()
 529
 530            yield node
 531
 532            if prune and prune(node):
 533                continue
 534
 535            for v in node.iter_expressions():
 536                queue.append(v)
 537
 538    def unnest(self):
 539        """
 540        Returns the first non parenthesis child or self.
 541        """
 542        expression = self
 543        while type(expression) is Paren:
 544            expression = expression.this
 545        return expression
 546
 547    def unalias(self):
 548        """
 549        Returns the inner expression if this is an Alias.
 550        """
 551        if isinstance(self, Alias):
 552            return self.this
 553        return self
 554
 555    def unnest_operands(self):
 556        """
 557        Returns unnested operands as a tuple.
 558        """
 559        return tuple(arg.unnest() for arg in self.iter_expressions())
 560
 561    def flatten(self, unnest=True):
 562        """
 563        Returns a generator which yields child nodes whose parents are the same class.
 564
 565        A AND B AND C -> [A, B, C]
 566        """
 567        for node in self.dfs(prune=lambda n: n.parent and type(n) is not self.__class__):
 568            if type(node) is not self.__class__:
 569                yield node.unnest() if unnest and not isinstance(node, Subquery) else node
 570
 571    def __str__(self) -> str:
 572        return self.sql()
 573
 574    def __repr__(self) -> str:
 575        return _to_s(self)
 576
 577    def to_s(self) -> str:
 578        """
 579        Same as __repr__, but includes additional information which can be useful
 580        for debugging, like empty or missing args and the AST nodes' object IDs.
 581        """
 582        return _to_s(self, verbose=True)
 583
 584    def sql(self, dialect: DialectType = None, **opts) -> str:
 585        """
 586        Returns SQL string representation of this tree.
 587
 588        Args:
 589            dialect: the dialect of the output SQL string (eg. "spark", "hive", "presto", "mysql").
 590            opts: other `sqlglot.generator.Generator` options.
 591
 592        Returns:
 593            The SQL string.
 594        """
 595        from sqlglot.dialects import Dialect
 596
 597        return Dialect.get_or_raise(dialect).generate(self, **opts)
 598
 599    def transform(self, fun: t.Callable, *args: t.Any, copy: bool = True, **kwargs) -> Expression:
 600        """
 601        Visits all tree nodes (excluding already transformed ones)
 602        and applies the given transformation function to each node.
 603
 604        Args:
 605            fun: a function which takes a node as an argument and returns a
 606                new transformed node or the same node without modifications. If the function
 607                returns None, then the corresponding node will be removed from the syntax tree.
 608            copy: if set to True a new tree instance is constructed, otherwise the tree is
 609                modified in place.
 610
 611        Returns:
 612            The transformed tree.
 613        """
 614        root = None
 615        new_node = None
 616
 617        for node in (self.copy() if copy else self).dfs(prune=lambda n: n is not new_node):
 618            parent, arg_key, index = node.parent, node.arg_key, node.index
 619            new_node = fun(node, *args, **kwargs)
 620
 621            if not root:
 622                root = new_node
 623            elif new_node is not node:
 624                parent.set(arg_key, new_node, index)
 625
 626        assert root
 627        return root.assert_is(Expression)
 628
 629    @t.overload
 630    def replace(self, expression: E) -> E: ...
 631
 632    @t.overload
 633    def replace(self, expression: None) -> None: ...
 634
 635    def replace(self, expression):
 636        """
 637        Swap out this expression with a new expression.
 638
 639        For example::
 640
 641            >>> tree = Select().select("x").from_("tbl")
 642            >>> tree.find(Column).replace(column("y"))
 643            Column(
 644              this=Identifier(this=y, quoted=False))
 645            >>> tree.sql()
 646            'SELECT y FROM tbl'
 647
 648        Args:
 649            expression: new node
 650
 651        Returns:
 652            The new expression or expressions.
 653        """
 654        parent = self.parent
 655
 656        if not parent or parent is expression:
 657            return expression
 658
 659        key = self.arg_key
 660        value = parent.args.get(key)
 661
 662        if type(expression) is list and isinstance(value, Expression):
 663            # We are trying to replace an Expression with a list, so it's assumed that
 664            # the intention was to really replace the parent of this expression.
 665            value.parent.replace(expression)
 666        else:
 667            parent.set(key, expression, self.index)
 668
 669        if expression is not self:
 670            self.parent = None
 671            self.arg_key = None
 672            self.index = None
 673
 674        return expression
 675
 676    def pop(self: E) -> E:
 677        """
 678        Remove this expression from its AST.
 679
 680        Returns:
 681            The popped expression.
 682        """
 683        self.replace(None)
 684        return self
 685
 686    def assert_is(self, type_: t.Type[E]) -> E:
 687        """
 688        Assert that this `Expression` is an instance of `type_`.
 689
 690        If it is NOT an instance of `type_`, this raises an assertion error.
 691        Otherwise, this returns this expression.
 692
 693        Examples:
 694            This is useful for type security in chained expressions:
 695
 696            >>> import sqlglot
 697            >>> sqlglot.parse_one("SELECT x from y").assert_is(Select).select("z").sql()
 698            'SELECT x, z FROM y'
 699        """
 700        if not isinstance(self, type_):
 701            raise AssertionError(f"{self} is not {type_}.")
 702        return self
 703
 704    def error_messages(self, args: t.Optional[t.Sequence] = None) -> t.List[str]:
 705        """
 706        Checks if this expression is valid (e.g. all mandatory args are set).
 707
 708        Args:
 709            args: a sequence of values that were used to instantiate a Func expression. This is used
 710                to check that the provided arguments don't exceed the function argument limit.
 711
 712        Returns:
 713            A list of error messages for all possible errors that were found.
 714        """
 715        errors: t.List[str] = []
 716
 717        for k in self.args:
 718            if k not in self.arg_types:
 719                errors.append(f"Unexpected keyword: '{k}' for {self.__class__}")
 720        for k, mandatory in self.arg_types.items():
 721            v = self.args.get(k)
 722            if mandatory and (v is None or (isinstance(v, list) and not v)):
 723                errors.append(f"Required keyword: '{k}' missing for {self.__class__}")
 724
 725        if (
 726            args
 727            and isinstance(self, Func)
 728            and len(args) > len(self.arg_types)
 729            and not self.is_var_len_args
 730        ):
 731            errors.append(
 732                f"The number of provided arguments ({len(args)}) is greater than "
 733                f"the maximum number of supported arguments ({len(self.arg_types)})"
 734            )
 735
 736        return errors
 737
 738    def dump(self):
 739        """
 740        Dump this Expression to a JSON-serializable dict.
 741        """
 742        from sqlglot.serde import dump
 743
 744        return dump(self)
 745
 746    @classmethod
 747    def load(cls, obj):
 748        """
 749        Load a dict (as returned by `Expression.dump`) into an Expression instance.
 750        """
 751        from sqlglot.serde import load
 752
 753        return load(obj)
 754
 755    def and_(
 756        self,
 757        *expressions: t.Optional[ExpOrStr],
 758        dialect: DialectType = None,
 759        copy: bool = True,
 760        **opts,
 761    ) -> Condition:
 762        """
 763        AND this condition with one or multiple expressions.
 764
 765        Example:
 766            >>> condition("x=1").and_("y=1").sql()
 767            'x = 1 AND y = 1'
 768
 769        Args:
 770            *expressions: the SQL code strings to parse.
 771                If an `Expression` instance is passed, it will be used as-is.
 772            dialect: the dialect used to parse the input expression.
 773            copy: whether to copy the involved expressions (only applies to Expressions).
 774            opts: other options to use to parse the input expressions.
 775
 776        Returns:
 777            The new And condition.
 778        """
 779        return and_(self, *expressions, dialect=dialect, copy=copy, **opts)
 780
 781    def or_(
 782        self,
 783        *expressions: t.Optional[ExpOrStr],
 784        dialect: DialectType = None,
 785        copy: bool = True,
 786        **opts,
 787    ) -> Condition:
 788        """
 789        OR this condition with one or multiple expressions.
 790
 791        Example:
 792            >>> condition("x=1").or_("y=1").sql()
 793            'x = 1 OR y = 1'
 794
 795        Args:
 796            *expressions: the SQL code strings to parse.
 797                If an `Expression` instance is passed, it will be used as-is.
 798            dialect: the dialect used to parse the input expression.
 799            copy: whether to copy the involved expressions (only applies to Expressions).
 800            opts: other options to use to parse the input expressions.
 801
 802        Returns:
 803            The new Or condition.
 804        """
 805        return or_(self, *expressions, dialect=dialect, copy=copy, **opts)
 806
 807    def not_(self, copy: bool = True):
 808        """
 809        Wrap this condition with NOT.
 810
 811        Example:
 812            >>> condition("x=1").not_().sql()
 813            'NOT x = 1'
 814
 815        Args:
 816            copy: whether to copy this object.
 817
 818        Returns:
 819            The new Not instance.
 820        """
 821        return not_(self, copy=copy)
 822
 823    def as_(
 824        self,
 825        alias: str | Identifier,
 826        quoted: t.Optional[bool] = None,
 827        dialect: DialectType = None,
 828        copy: bool = True,
 829        **opts,
 830    ) -> Alias:
 831        return alias_(self, alias, quoted=quoted, dialect=dialect, copy=copy, **opts)
 832
 833    def _binop(self, klass: t.Type[E], other: t.Any, reverse: bool = False) -> E:
 834        this = self.copy()
 835        other = convert(other, copy=True)
 836        if not isinstance(this, klass) and not isinstance(other, klass):
 837            this = _wrap(this, Binary)
 838            other = _wrap(other, Binary)
 839        if reverse:
 840            return klass(this=other, expression=this)
 841        return klass(this=this, expression=other)
 842
 843    def __getitem__(self, other: ExpOrStr | t.Tuple[ExpOrStr]) -> Bracket:
 844        return Bracket(
 845            this=self.copy(), expressions=[convert(e, copy=True) for e in ensure_list(other)]
 846        )
 847
 848    def __iter__(self) -> t.Iterator:
 849        if "expressions" in self.arg_types:
 850            return iter(self.args.get("expressions") or [])
 851        # We define this because __getitem__ converts Expression into an iterable, which is
 852        # problematic because one can hit infinite loops if they do "for x in some_expr: ..."
 853        # See: https://peps.python.org/pep-0234/
 854        raise TypeError(f"'{self.__class__.__name__}' object is not iterable")
 855
 856    def isin(
 857        self,
 858        *expressions: t.Any,
 859        query: t.Optional[ExpOrStr] = None,
 860        unnest: t.Optional[ExpOrStr] | t.Collection[ExpOrStr] = None,
 861        copy: bool = True,
 862        **opts,
 863    ) -> In:
 864        subquery = maybe_parse(query, copy=copy, **opts) if query else None
 865        if subquery and not isinstance(subquery, Subquery):
 866            subquery = subquery.subquery(copy=False)
 867
 868        return In(
 869            this=maybe_copy(self, copy),
 870            expressions=[convert(e, copy=copy) for e in expressions],
 871            query=subquery,
 872            unnest=(
 873                Unnest(
 874                    expressions=[
 875                        maybe_parse(t.cast(ExpOrStr, e), copy=copy, **opts)
 876                        for e in ensure_list(unnest)
 877                    ]
 878                )
 879                if unnest
 880                else None
 881            ),
 882        )
 883
 884    def between(self, low: t.Any, high: t.Any, copy: bool = True, **opts) -> Between:
 885        return Between(
 886            this=maybe_copy(self, copy),
 887            low=convert(low, copy=copy, **opts),
 888            high=convert(high, copy=copy, **opts),
 889        )
 890
 891    def is_(self, other: ExpOrStr) -> Is:
 892        return self._binop(Is, other)
 893
 894    def like(self, other: ExpOrStr) -> Like:
 895        return self._binop(Like, other)
 896
 897    def ilike(self, other: ExpOrStr) -> ILike:
 898        return self._binop(ILike, other)
 899
 900    def eq(self, other: t.Any) -> EQ:
 901        return self._binop(EQ, other)
 902
 903    def neq(self, other: t.Any) -> NEQ:
 904        return self._binop(NEQ, other)
 905
 906    def rlike(self, other: ExpOrStr) -> RegexpLike:
 907        return self._binop(RegexpLike, other)
 908
 909    def div(self, other: ExpOrStr, typed: bool = False, safe: bool = False) -> Div:
 910        div = self._binop(Div, other)
 911        div.args["typed"] = typed
 912        div.args["safe"] = safe
 913        return div
 914
 915    def asc(self, nulls_first: bool = True) -> Ordered:
 916        return Ordered(this=self.copy(), nulls_first=nulls_first)
 917
 918    def desc(self, nulls_first: bool = False) -> Ordered:
 919        return Ordered(this=self.copy(), desc=True, nulls_first=nulls_first)
 920
 921    def __lt__(self, other: t.Any) -> LT:
 922        return self._binop(LT, other)
 923
 924    def __le__(self, other: t.Any) -> LTE:
 925        return self._binop(LTE, other)
 926
 927    def __gt__(self, other: t.Any) -> GT:
 928        return self._binop(GT, other)
 929
 930    def __ge__(self, other: t.Any) -> GTE:
 931        return self._binop(GTE, other)
 932
 933    def __add__(self, other: t.Any) -> Add:
 934        return self._binop(Add, other)
 935
 936    def __radd__(self, other: t.Any) -> Add:
 937        return self._binop(Add, other, reverse=True)
 938
 939    def __sub__(self, other: t.Any) -> Sub:
 940        return self._binop(Sub, other)
 941
 942    def __rsub__(self, other: t.Any) -> Sub:
 943        return self._binop(Sub, other, reverse=True)
 944
 945    def __mul__(self, other: t.Any) -> Mul:
 946        return self._binop(Mul, other)
 947
 948    def __rmul__(self, other: t.Any) -> Mul:
 949        return self._binop(Mul, other, reverse=True)
 950
 951    def __truediv__(self, other: t.Any) -> Div:
 952        return self._binop(Div, other)
 953
 954    def __rtruediv__(self, other: t.Any) -> Div:
 955        return self._binop(Div, other, reverse=True)
 956
 957    def __floordiv__(self, other: t.Any) -> IntDiv:
 958        return self._binop(IntDiv, other)
 959
 960    def __rfloordiv__(self, other: t.Any) -> IntDiv:
 961        return self._binop(IntDiv, other, reverse=True)
 962
 963    def __mod__(self, other: t.Any) -> Mod:
 964        return self._binop(Mod, other)
 965
 966    def __rmod__(self, other: t.Any) -> Mod:
 967        return self._binop(Mod, other, reverse=True)
 968
 969    def __pow__(self, other: t.Any) -> Pow:
 970        return self._binop(Pow, other)
 971
 972    def __rpow__(self, other: t.Any) -> Pow:
 973        return self._binop(Pow, other, reverse=True)
 974
 975    def __and__(self, other: t.Any) -> And:
 976        return self._binop(And, other)
 977
 978    def __rand__(self, other: t.Any) -> And:
 979        return self._binop(And, other, reverse=True)
 980
 981    def __or__(self, other: t.Any) -> Or:
 982        return self._binop(Or, other)
 983
 984    def __ror__(self, other: t.Any) -> Or:
 985        return self._binop(Or, other, reverse=True)
 986
 987    def __neg__(self) -> Neg:
 988        return Neg(this=_wrap(self.copy(), Binary))
 989
 990    def __invert__(self) -> Not:
 991        return not_(self.copy())
 992
 993
 994IntoType = t.Union[
 995    str,
 996    t.Type[Expression],
 997    t.Collection[t.Union[str, t.Type[Expression]]],
 998]
 999ExpOrStr = t.Union[str, Expression]
1000
1001
1002class Condition(Expression):
1003    """Logical conditions like x AND y, or simply x"""
1004
1005
1006class Predicate(Condition):
1007    """Relationships like x = y, x > 1, x >= y."""
1008
1009
1010class DerivedTable(Expression):
1011    @property
1012    def selects(self) -> t.List[Expression]:
1013        return self.this.selects if isinstance(self.this, Query) else []
1014
1015    @property
1016    def named_selects(self) -> t.List[str]:
1017        return [select.output_name for select in self.selects]
1018
1019
1020class Query(Expression):
1021    def subquery(self, alias: t.Optional[ExpOrStr] = None, copy: bool = True) -> Subquery:
1022        """
1023        Returns a `Subquery` that wraps around this query.
1024
1025        Example:
1026            >>> subquery = Select().select("x").from_("tbl").subquery()
1027            >>> Select().select("x").from_(subquery).sql()
1028            'SELECT x FROM (SELECT x FROM tbl)'
1029
1030        Args:
1031            alias: an optional alias for the subquery.
1032            copy: if `False`, modify this expression instance in-place.
1033        """
1034        instance = maybe_copy(self, copy)
1035        if not isinstance(alias, Expression):
1036            alias = TableAlias(this=to_identifier(alias)) if alias else None
1037
1038        return Subquery(this=instance, alias=alias)
1039
1040    def limit(
1041        self: Q, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
1042    ) -> Q:
1043        """
1044        Adds a LIMIT clause to this query.
1045
1046        Example:
1047            >>> select("1").union(select("1")).limit(1).sql()
1048            'SELECT 1 UNION SELECT 1 LIMIT 1'
1049
1050        Args:
1051            expression: the SQL code string to parse.
1052                This can also be an integer.
1053                If a `Limit` instance is passed, it will be used as-is.
1054                If another `Expression` instance is passed, it will be wrapped in a `Limit`.
1055            dialect: the dialect used to parse the input expression.
1056            copy: if `False`, modify this expression instance in-place.
1057            opts: other options to use to parse the input expressions.
1058
1059        Returns:
1060            A limited Select expression.
1061        """
1062        return _apply_builder(
1063            expression=expression,
1064            instance=self,
1065            arg="limit",
1066            into=Limit,
1067            prefix="LIMIT",
1068            dialect=dialect,
1069            copy=copy,
1070            into_arg="expression",
1071            **opts,
1072        )
1073
1074    def offset(
1075        self: Q, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
1076    ) -> Q:
1077        """
1078        Set the OFFSET expression.
1079
1080        Example:
1081            >>> Select().from_("tbl").select("x").offset(10).sql()
1082            'SELECT x FROM tbl OFFSET 10'
1083
1084        Args:
1085            expression: the SQL code string to parse.
1086                This can also be an integer.
1087                If a `Offset` instance is passed, this is used as-is.
1088                If another `Expression` instance is passed, it will be wrapped in a `Offset`.
1089            dialect: the dialect used to parse the input expression.
1090            copy: if `False`, modify this expression instance in-place.
1091            opts: other options to use to parse the input expressions.
1092
1093        Returns:
1094            The modified Select expression.
1095        """
1096        return _apply_builder(
1097            expression=expression,
1098            instance=self,
1099            arg="offset",
1100            into=Offset,
1101            prefix="OFFSET",
1102            dialect=dialect,
1103            copy=copy,
1104            into_arg="expression",
1105            **opts,
1106        )
1107
1108    def order_by(
1109        self: Q,
1110        *expressions: t.Optional[ExpOrStr],
1111        append: bool = True,
1112        dialect: DialectType = None,
1113        copy: bool = True,
1114        **opts,
1115    ) -> Q:
1116        """
1117        Set the ORDER BY expression.
1118
1119        Example:
1120            >>> Select().from_("tbl").select("x").order_by("x DESC").sql()
1121            'SELECT x FROM tbl ORDER BY x DESC'
1122
1123        Args:
1124            *expressions: the SQL code strings to parse.
1125                If a `Group` instance is passed, this is used as-is.
1126                If another `Expression` instance is passed, it will be wrapped in a `Order`.
1127            append: if `True`, add to any existing expressions.
1128                Otherwise, this flattens all the `Order` expression into a single expression.
1129            dialect: the dialect used to parse the input expression.
1130            copy: if `False`, modify this expression instance in-place.
1131            opts: other options to use to parse the input expressions.
1132
1133        Returns:
1134            The modified Select expression.
1135        """
1136        return _apply_child_list_builder(
1137            *expressions,
1138            instance=self,
1139            arg="order",
1140            append=append,
1141            copy=copy,
1142            prefix="ORDER BY",
1143            into=Order,
1144            dialect=dialect,
1145            **opts,
1146        )
1147
1148    @property
1149    def ctes(self) -> t.List[CTE]:
1150        """Returns a list of all the CTEs attached to this query."""
1151        with_ = self.args.get("with")
1152        return with_.expressions if with_ else []
1153
1154    @property
1155    def selects(self) -> t.List[Expression]:
1156        """Returns the query's projections."""
1157        raise NotImplementedError("Query objects must implement `selects`")
1158
1159    @property
1160    def named_selects(self) -> t.List[str]:
1161        """Returns the output names of the query's projections."""
1162        raise NotImplementedError("Query objects must implement `named_selects`")
1163
1164    def select(
1165        self: Q,
1166        *expressions: t.Optional[ExpOrStr],
1167        append: bool = True,
1168        dialect: DialectType = None,
1169        copy: bool = True,
1170        **opts,
1171    ) -> Q:
1172        """
1173        Append to or set the SELECT expressions.
1174
1175        Example:
1176            >>> Select().select("x", "y").sql()
1177            'SELECT x, y'
1178
1179        Args:
1180            *expressions: the SQL code strings to parse.
1181                If an `Expression` instance is passed, it will be used as-is.
1182            append: if `True`, add to any existing expressions.
1183                Otherwise, this resets the expressions.
1184            dialect: the dialect used to parse the input expressions.
1185            copy: if `False`, modify this expression instance in-place.
1186            opts: other options to use to parse the input expressions.
1187
1188        Returns:
1189            The modified Query expression.
1190        """
1191        raise NotImplementedError("Query objects must implement `select`")
1192
1193    def with_(
1194        self: Q,
1195        alias: ExpOrStr,
1196        as_: ExpOrStr,
1197        recursive: t.Optional[bool] = None,
1198        append: bool = True,
1199        dialect: DialectType = None,
1200        copy: bool = True,
1201        **opts,
1202    ) -> Q:
1203        """
1204        Append to or set the common table expressions.
1205
1206        Example:
1207            >>> Select().with_("tbl2", as_="SELECT * FROM tbl").select("x").from_("tbl2").sql()
1208            'WITH tbl2 AS (SELECT * FROM tbl) SELECT x FROM tbl2'
1209
1210        Args:
1211            alias: the SQL code string to parse as the table name.
1212                If an `Expression` instance is passed, this is used as-is.
1213            as_: the SQL code string to parse as the table expression.
1214                If an `Expression` instance is passed, it will be used as-is.
1215            recursive: set the RECURSIVE part of the expression. Defaults to `False`.
1216            append: if `True`, add to any existing expressions.
1217                Otherwise, this resets the expressions.
1218            dialect: the dialect used to parse the input expression.
1219            copy: if `False`, modify this expression instance in-place.
1220            opts: other options to use to parse the input expressions.
1221
1222        Returns:
1223            The modified expression.
1224        """
1225        return _apply_cte_builder(
1226            self, alias, as_, recursive=recursive, append=append, dialect=dialect, copy=copy, **opts
1227        )
1228
1229    def union(
1230        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
1231    ) -> Union:
1232        """
1233        Builds a UNION expression.
1234
1235        Example:
1236            >>> import sqlglot
1237            >>> sqlglot.parse_one("SELECT * FROM foo").union("SELECT * FROM bla").sql()
1238            'SELECT * FROM foo UNION SELECT * FROM bla'
1239
1240        Args:
1241            expression: the SQL code string.
1242                If an `Expression` instance is passed, it will be used as-is.
1243            distinct: set the DISTINCT flag if and only if this is true.
1244            dialect: the dialect used to parse the input expression.
1245            opts: other options to use to parse the input expressions.
1246
1247        Returns:
1248            The new Union expression.
1249        """
1250        return union(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
1251
1252    def intersect(
1253        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
1254    ) -> Intersect:
1255        """
1256        Builds an INTERSECT expression.
1257
1258        Example:
1259            >>> import sqlglot
1260            >>> sqlglot.parse_one("SELECT * FROM foo").intersect("SELECT * FROM bla").sql()
1261            'SELECT * FROM foo INTERSECT SELECT * FROM bla'
1262
1263        Args:
1264            expression: the SQL code string.
1265                If an `Expression` instance is passed, it will be used as-is.
1266            distinct: set the DISTINCT flag if and only if this is true.
1267            dialect: the dialect used to parse the input expression.
1268            opts: other options to use to parse the input expressions.
1269
1270        Returns:
1271            The new Intersect expression.
1272        """
1273        return intersect(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
1274
1275    def except_(
1276        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
1277    ) -> Except:
1278        """
1279        Builds an EXCEPT expression.
1280
1281        Example:
1282            >>> import sqlglot
1283            >>> sqlglot.parse_one("SELECT * FROM foo").except_("SELECT * FROM bla").sql()
1284            'SELECT * FROM foo EXCEPT SELECT * FROM bla'
1285
1286        Args:
1287            expression: the SQL code string.
1288                If an `Expression` instance is passed, it will be used as-is.
1289            distinct: set the DISTINCT flag if and only if this is true.
1290            dialect: the dialect used to parse the input expression.
1291            opts: other options to use to parse the input expressions.
1292
1293        Returns:
1294            The new Except expression.
1295        """
1296        return except_(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
1297
1298
1299class UDTF(DerivedTable):
1300    @property
1301    def selects(self) -> t.List[Expression]:
1302        alias = self.args.get("alias")
1303        return alias.columns if alias else []
1304
1305
1306class Cache(Expression):
1307    arg_types = {
1308        "this": True,
1309        "lazy": False,
1310        "options": False,
1311        "expression": False,
1312    }
1313
1314
1315class Uncache(Expression):
1316    arg_types = {"this": True, "exists": False}
1317
1318
1319class Refresh(Expression):
1320    pass
1321
1322
1323class DDL(Expression):
1324    @property
1325    def ctes(self) -> t.List[CTE]:
1326        """Returns a list of all the CTEs attached to this statement."""
1327        with_ = self.args.get("with")
1328        return with_.expressions if with_ else []
1329
1330    @property
1331    def selects(self) -> t.List[Expression]:
1332        """If this statement contains a query (e.g. a CTAS), this returns the query's projections."""
1333        return self.expression.selects if isinstance(self.expression, Query) else []
1334
1335    @property
1336    def named_selects(self) -> t.List[str]:
1337        """
1338        If this statement contains a query (e.g. a CTAS), this returns the output
1339        names of the query's projections.
1340        """
1341        return self.expression.named_selects if isinstance(self.expression, Query) else []
1342
1343
1344class DML(Expression):
1345    def returning(
1346        self,
1347        expression: ExpOrStr,
1348        dialect: DialectType = None,
1349        copy: bool = True,
1350        **opts,
1351    ) -> DML:
1352        """
1353        Set the RETURNING expression. Not supported by all dialects.
1354
1355        Example:
1356            >>> delete("tbl").returning("*", dialect="postgres").sql()
1357            'DELETE FROM tbl RETURNING *'
1358
1359        Args:
1360            expression: the SQL code strings to parse.
1361                If an `Expression` instance is passed, it will be used as-is.
1362            dialect: the dialect used to parse the input expressions.
1363            copy: if `False`, modify this expression instance in-place.
1364            opts: other options to use to parse the input expressions.
1365
1366        Returns:
1367            Delete: the modified expression.
1368        """
1369        return _apply_builder(
1370            expression=expression,
1371            instance=self,
1372            arg="returning",
1373            prefix="RETURNING",
1374            dialect=dialect,
1375            copy=copy,
1376            into=Returning,
1377            **opts,
1378        )
1379
1380
1381class Create(DDL):
1382    arg_types = {
1383        "with": False,
1384        "this": True,
1385        "kind": True,
1386        "expression": False,
1387        "exists": False,
1388        "properties": False,
1389        "replace": False,
1390        "unique": False,
1391        "indexes": False,
1392        "no_schema_binding": False,
1393        "begin": False,
1394        "end": False,
1395        "clone": False,
1396    }
1397
1398    @property
1399    def kind(self) -> t.Optional[str]:
1400        kind = self.args.get("kind")
1401        return kind and kind.upper()
1402
1403
1404class SequenceProperties(Expression):
1405    arg_types = {
1406        "increment": False,
1407        "minvalue": False,
1408        "maxvalue": False,
1409        "cache": False,
1410        "start": False,
1411        "owned": False,
1412        "options": False,
1413    }
1414
1415
1416class TruncateTable(Expression):
1417    arg_types = {
1418        "expressions": True,
1419        "is_database": False,
1420        "exists": False,
1421        "only": False,
1422        "cluster": False,
1423        "identity": False,
1424        "option": False,
1425        "partition": False,
1426    }
1427
1428
1429# https://docs.snowflake.com/en/sql-reference/sql/create-clone
1430# https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#create_table_clone_statement
1431# https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#create_table_copy
1432class Clone(Expression):
1433    arg_types = {"this": True, "shallow": False, "copy": False}
1434
1435
1436class Describe(Expression):
1437    arg_types = {"this": True, "style": False, "kind": False, "expressions": False}
1438
1439
1440class Kill(Expression):
1441    arg_types = {"this": True, "kind": False}
1442
1443
1444class Pragma(Expression):
1445    pass
1446
1447
1448class Set(Expression):
1449    arg_types = {"expressions": False, "unset": False, "tag": False}
1450
1451
1452class Heredoc(Expression):
1453    arg_types = {"this": True, "tag": False}
1454
1455
1456class SetItem(Expression):
1457    arg_types = {
1458        "this": False,
1459        "expressions": False,
1460        "kind": False,
1461        "collate": False,  # MySQL SET NAMES statement
1462        "global": False,
1463    }
1464
1465
1466class Show(Expression):
1467    arg_types = {
1468        "this": True,
1469        "history": False,
1470        "terse": False,
1471        "target": False,
1472        "offset": False,
1473        "starts_with": False,
1474        "limit": False,
1475        "from": False,
1476        "like": False,
1477        "where": False,
1478        "db": False,
1479        "scope": False,
1480        "scope_kind": False,
1481        "full": False,
1482        "mutex": False,
1483        "query": False,
1484        "channel": False,
1485        "global": False,
1486        "log": False,
1487        "position": False,
1488        "types": False,
1489    }
1490
1491
1492class UserDefinedFunction(Expression):
1493    arg_types = {"this": True, "expressions": False, "wrapped": False}
1494
1495
1496class CharacterSet(Expression):
1497    arg_types = {"this": True, "default": False}
1498
1499
1500class With(Expression):
1501    arg_types = {"expressions": True, "recursive": False}
1502
1503    @property
1504    def recursive(self) -> bool:
1505        return bool(self.args.get("recursive"))
1506
1507
1508class WithinGroup(Expression):
1509    arg_types = {"this": True, "expression": False}
1510
1511
1512# clickhouse supports scalar ctes
1513# https://clickhouse.com/docs/en/sql-reference/statements/select/with
1514class CTE(DerivedTable):
1515    arg_types = {
1516        "this": True,
1517        "alias": True,
1518        "scalar": False,
1519        "materialized": False,
1520    }
1521
1522
1523class TableAlias(Expression):
1524    arg_types = {"this": False, "columns": False}
1525
1526    @property
1527    def columns(self):
1528        return self.args.get("columns") or []
1529
1530
1531class BitString(Condition):
1532    pass
1533
1534
1535class HexString(Condition):
1536    pass
1537
1538
1539class ByteString(Condition):
1540    pass
1541
1542
1543class RawString(Condition):
1544    pass
1545
1546
1547class UnicodeString(Condition):
1548    arg_types = {"this": True, "escape": False}
1549
1550
1551class Column(Condition):
1552    arg_types = {"this": True, "table": False, "db": False, "catalog": False, "join_mark": False}
1553
1554    @property
1555    def table(self) -> str:
1556        return self.text("table")
1557
1558    @property
1559    def db(self) -> str:
1560        return self.text("db")
1561
1562    @property
1563    def catalog(self) -> str:
1564        return self.text("catalog")
1565
1566    @property
1567    def output_name(self) -> str:
1568        return self.name
1569
1570    @property
1571    def parts(self) -> t.List[Identifier]:
1572        """Return the parts of a column in order catalog, db, table, name."""
1573        return [
1574            t.cast(Identifier, self.args[part])
1575            for part in ("catalog", "db", "table", "this")
1576            if self.args.get(part)
1577        ]
1578
1579    def to_dot(self) -> Dot | Identifier:
1580        """Converts the column into a dot expression."""
1581        parts = self.parts
1582        parent = self.parent
1583
1584        while parent:
1585            if isinstance(parent, Dot):
1586                parts.append(parent.expression)
1587            parent = parent.parent
1588
1589        return Dot.build(deepcopy(parts)) if len(parts) > 1 else parts[0]
1590
1591
1592class ColumnPosition(Expression):
1593    arg_types = {"this": False, "position": True}
1594
1595
1596class ColumnDef(Expression):
1597    arg_types = {
1598        "this": True,
1599        "kind": False,
1600        "constraints": False,
1601        "exists": False,
1602        "position": False,
1603    }
1604
1605    @property
1606    def constraints(self) -> t.List[ColumnConstraint]:
1607        return self.args.get("constraints") or []
1608
1609    @property
1610    def kind(self) -> t.Optional[DataType]:
1611        return self.args.get("kind")
1612
1613
1614class AlterColumn(Expression):
1615    arg_types = {
1616        "this": True,
1617        "dtype": False,
1618        "collate": False,
1619        "using": False,
1620        "default": False,
1621        "drop": False,
1622        "comment": False,
1623    }
1624
1625
1626class RenameColumn(Expression):
1627    arg_types = {"this": True, "to": True, "exists": False}
1628
1629
1630class RenameTable(Expression):
1631    pass
1632
1633
1634class SwapTable(Expression):
1635    pass
1636
1637
1638class Comment(Expression):
1639    arg_types = {
1640        "this": True,
1641        "kind": True,
1642        "expression": True,
1643        "exists": False,
1644        "materialized": False,
1645    }
1646
1647
1648class Comprehension(Expression):
1649    arg_types = {"this": True, "expression": True, "iterator": True, "condition": False}
1650
1651
1652# https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#mergetree-table-ttl
1653class MergeTreeTTLAction(Expression):
1654    arg_types = {
1655        "this": True,
1656        "delete": False,
1657        "recompress": False,
1658        "to_disk": False,
1659        "to_volume": False,
1660    }
1661
1662
1663# https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#mergetree-table-ttl
1664class MergeTreeTTL(Expression):
1665    arg_types = {
1666        "expressions": True,
1667        "where": False,
1668        "group": False,
1669        "aggregates": False,
1670    }
1671
1672
1673# https://dev.mysql.com/doc/refman/8.0/en/create-table.html
1674class IndexConstraintOption(Expression):
1675    arg_types = {
1676        "key_block_size": False,
1677        "using": False,
1678        "parser": False,
1679        "comment": False,
1680        "visible": False,
1681        "engine_attr": False,
1682        "secondary_engine_attr": False,
1683    }
1684
1685
1686class ColumnConstraint(Expression):
1687    arg_types = {"this": False, "kind": True}
1688
1689    @property
1690    def kind(self) -> ColumnConstraintKind:
1691        return self.args["kind"]
1692
1693
1694class ColumnConstraintKind(Expression):
1695    pass
1696
1697
1698class AutoIncrementColumnConstraint(ColumnConstraintKind):
1699    pass
1700
1701
1702class PeriodForSystemTimeConstraint(ColumnConstraintKind):
1703    arg_types = {"this": True, "expression": True}
1704
1705
1706class CaseSpecificColumnConstraint(ColumnConstraintKind):
1707    arg_types = {"not_": True}
1708
1709
1710class CharacterSetColumnConstraint(ColumnConstraintKind):
1711    arg_types = {"this": True}
1712
1713
1714class CheckColumnConstraint(ColumnConstraintKind):
1715    arg_types = {"this": True, "enforced": False}
1716
1717
1718class ClusteredColumnConstraint(ColumnConstraintKind):
1719    pass
1720
1721
1722class CollateColumnConstraint(ColumnConstraintKind):
1723    pass
1724
1725
1726class CommentColumnConstraint(ColumnConstraintKind):
1727    pass
1728
1729
1730class CompressColumnConstraint(ColumnConstraintKind):
1731    pass
1732
1733
1734class DateFormatColumnConstraint(ColumnConstraintKind):
1735    arg_types = {"this": True}
1736
1737
1738class DefaultColumnConstraint(ColumnConstraintKind):
1739    pass
1740
1741
1742class EncodeColumnConstraint(ColumnConstraintKind):
1743    pass
1744
1745
1746# https://www.postgresql.org/docs/current/sql-createtable.html#SQL-CREATETABLE-EXCLUDE
1747class ExcludeColumnConstraint(ColumnConstraintKind):
1748    pass
1749
1750
1751class EphemeralColumnConstraint(ColumnConstraintKind):
1752    arg_types = {"this": False}
1753
1754
1755class WithOperator(Expression):
1756    arg_types = {"this": True, "op": True}
1757
1758
1759class GeneratedAsIdentityColumnConstraint(ColumnConstraintKind):
1760    # this: True -> ALWAYS, this: False -> BY DEFAULT
1761    arg_types = {
1762        "this": False,
1763        "expression": False,
1764        "on_null": False,
1765        "start": False,
1766        "increment": False,
1767        "minvalue": False,
1768        "maxvalue": False,
1769        "cycle": False,
1770    }
1771
1772
1773class GeneratedAsRowColumnConstraint(ColumnConstraintKind):
1774    arg_types = {"start": False, "hidden": False}
1775
1776
1777# https://dev.mysql.com/doc/refman/8.0/en/create-table.html
1778# https://github.com/ClickHouse/ClickHouse/blob/master/src/Parsers/ParserCreateQuery.h#L646
1779class IndexColumnConstraint(ColumnConstraintKind):
1780    arg_types = {
1781        "this": False,
1782        "expressions": False,
1783        "kind": False,
1784        "index_type": False,
1785        "options": False,
1786        "expression": False,  # Clickhouse
1787        "granularity": False,
1788    }
1789
1790
1791class InlineLengthColumnConstraint(ColumnConstraintKind):
1792    pass
1793
1794
1795class NonClusteredColumnConstraint(ColumnConstraintKind):
1796    pass
1797
1798
1799class NotForReplicationColumnConstraint(ColumnConstraintKind):
1800    arg_types = {}
1801
1802
1803class NotNullColumnConstraint(ColumnConstraintKind):
1804    arg_types = {"allow_null": False}
1805
1806
1807# https://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html
1808class OnUpdateColumnConstraint(ColumnConstraintKind):
1809    pass
1810
1811
1812# https://docs.snowflake.com/en/sql-reference/sql/create-external-table#optional-parameters
1813class TransformColumnConstraint(ColumnConstraintKind):
1814    pass
1815
1816
1817class PrimaryKeyColumnConstraint(ColumnConstraintKind):
1818    arg_types = {"desc": False}
1819
1820
1821class TitleColumnConstraint(ColumnConstraintKind):
1822    pass
1823
1824
1825class UniqueColumnConstraint(ColumnConstraintKind):
1826    arg_types = {"this": False, "index_type": False, "on_conflict": False}
1827
1828
1829class UppercaseColumnConstraint(ColumnConstraintKind):
1830    arg_types: t.Dict[str, t.Any] = {}
1831
1832
1833class PathColumnConstraint(ColumnConstraintKind):
1834    pass
1835
1836
1837# computed column expression
1838# https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql?view=sql-server-ver16
1839class ComputedColumnConstraint(ColumnConstraintKind):
1840    arg_types = {"this": True, "persisted": False, "not_null": False}
1841
1842
1843class Constraint(Expression):
1844    arg_types = {"this": True, "expressions": True}
1845
1846
1847class Delete(DML):
1848    arg_types = {
1849        "with": False,
1850        "this": False,
1851        "using": False,
1852        "where": False,
1853        "returning": False,
1854        "limit": False,
1855        "tables": False,  # Multiple-Table Syntax (MySQL)
1856    }
1857
1858    def delete(
1859        self,
1860        table: ExpOrStr,
1861        dialect: DialectType = None,
1862        copy: bool = True,
1863        **opts,
1864    ) -> Delete:
1865        """
1866        Create a DELETE expression or replace the table on an existing DELETE expression.
1867
1868        Example:
1869            >>> delete("tbl").sql()
1870            'DELETE FROM tbl'
1871
1872        Args:
1873            table: the table from which to delete.
1874            dialect: the dialect used to parse the input expression.
1875            copy: if `False`, modify this expression instance in-place.
1876            opts: other options to use to parse the input expressions.
1877
1878        Returns:
1879            Delete: the modified expression.
1880        """
1881        return _apply_builder(
1882            expression=table,
1883            instance=self,
1884            arg="this",
1885            dialect=dialect,
1886            into=Table,
1887            copy=copy,
1888            **opts,
1889        )
1890
1891    def where(
1892        self,
1893        *expressions: t.Optional[ExpOrStr],
1894        append: bool = True,
1895        dialect: DialectType = None,
1896        copy: bool = True,
1897        **opts,
1898    ) -> Delete:
1899        """
1900        Append to or set the WHERE expressions.
1901
1902        Example:
1903            >>> delete("tbl").where("x = 'a' OR x < 'b'").sql()
1904            "DELETE FROM tbl WHERE x = 'a' OR x < 'b'"
1905
1906        Args:
1907            *expressions: the SQL code strings to parse.
1908                If an `Expression` instance is passed, it will be used as-is.
1909                Multiple expressions are combined with an AND operator.
1910            append: if `True`, AND the new expressions to any existing expression.
1911                Otherwise, this resets the expression.
1912            dialect: the dialect used to parse the input expressions.
1913            copy: if `False`, modify this expression instance in-place.
1914            opts: other options to use to parse the input expressions.
1915
1916        Returns:
1917            Delete: the modified expression.
1918        """
1919        return _apply_conjunction_builder(
1920            *expressions,
1921            instance=self,
1922            arg="where",
1923            append=append,
1924            into=Where,
1925            dialect=dialect,
1926            copy=copy,
1927            **opts,
1928        )
1929
1930
1931class Drop(Expression):
1932    arg_types = {
1933        "this": False,
1934        "kind": False,
1935        "expressions": False,
1936        "exists": False,
1937        "temporary": False,
1938        "materialized": False,
1939        "cascade": False,
1940        "constraints": False,
1941        "purge": False,
1942    }
1943
1944
1945class Filter(Expression):
1946    arg_types = {"this": True, "expression": True}
1947
1948
1949class Check(Expression):
1950    pass
1951
1952
1953# https://docs.snowflake.com/en/sql-reference/constructs/connect-by
1954class Connect(Expression):
1955    arg_types = {"start": False, "connect": True, "nocycle": False}
1956
1957
1958class CopyParameter(Expression):
1959    arg_types = {"this": True, "expression": False}
1960
1961
1962class Copy(Expression):
1963    arg_types = {
1964        "this": True,
1965        "kind": True,
1966        "files": True,
1967        "credentials": False,
1968        "format": False,
1969        "params": False,
1970    }
1971
1972
1973class Credentials(Expression):
1974    arg_types = {
1975        "credentials": False,
1976        "encryption": False,
1977        "storage": False,
1978        "iam_role": False,
1979        "region": False,
1980    }
1981
1982
1983class Prior(Expression):
1984    pass
1985
1986
1987class Directory(Expression):
1988    # https://spark.apache.org/docs/3.0.0-preview/sql-ref-syntax-dml-insert-overwrite-directory-hive.html
1989    arg_types = {"this": True, "local": False, "row_format": False}
1990
1991
1992class ForeignKey(Expression):
1993    arg_types = {
1994        "expressions": True,
1995        "reference": False,
1996        "delete": False,
1997        "update": False,
1998    }
1999
2000
2001class ColumnPrefix(Expression):
2002    arg_types = {"this": True, "expression": True}
2003
2004
2005class PrimaryKey(Expression):
2006    arg_types = {"expressions": True, "options": False}
2007
2008
2009# https://www.postgresql.org/docs/9.1/sql-selectinto.html
2010# https://docs.aws.amazon.com/redshift/latest/dg/r_SELECT_INTO.html#r_SELECT_INTO-examples
2011class Into(Expression):
2012    arg_types = {"this": True, "temporary": False, "unlogged": False}
2013
2014
2015class From(Expression):
2016    @property
2017    def name(self) -> str:
2018        return self.this.name
2019
2020    @property
2021    def alias_or_name(self) -> str:
2022        return self.this.alias_or_name
2023
2024
2025class Having(Expression):
2026    pass
2027
2028
2029class Hint(Expression):
2030    arg_types = {"expressions": True}
2031
2032
2033class JoinHint(Expression):
2034    arg_types = {"this": True, "expressions": True}
2035
2036
2037class Identifier(Expression):
2038    arg_types = {"this": True, "quoted": False, "global": False, "temporary": False}
2039
2040    @property
2041    def quoted(self) -> bool:
2042        return bool(self.args.get("quoted"))
2043
2044    @property
2045    def hashable_args(self) -> t.Any:
2046        return (self.this, self.quoted)
2047
2048    @property
2049    def output_name(self) -> str:
2050        return self.name
2051
2052
2053# https://www.postgresql.org/docs/current/indexes-opclass.html
2054class Opclass(Expression):
2055    arg_types = {"this": True, "expression": True}
2056
2057
2058class Index(Expression):
2059    arg_types = {
2060        "this": False,
2061        "table": False,
2062        "unique": False,
2063        "primary": False,
2064        "amp": False,  # teradata
2065        "params": False,
2066    }
2067
2068
2069class IndexParameters(Expression):
2070    arg_types = {
2071        "using": False,
2072        "include": False,
2073        "columns": False,
2074        "with_storage": False,
2075        "partition_by": False,
2076        "tablespace": False,
2077        "where": False,
2078    }
2079
2080
2081class Insert(DDL, DML):
2082    arg_types = {
2083        "hint": False,
2084        "with": False,
2085        "is_function": False,
2086        "this": False,
2087        "expression": False,
2088        "conflict": False,
2089        "returning": False,
2090        "overwrite": False,
2091        "exists": False,
2092        "alternative": False,
2093        "where": False,
2094        "ignore": False,
2095        "by_name": False,
2096        "stored": False,
2097    }
2098
2099    def with_(
2100        self,
2101        alias: ExpOrStr,
2102        as_: ExpOrStr,
2103        recursive: t.Optional[bool] = None,
2104        append: bool = True,
2105        dialect: DialectType = None,
2106        copy: bool = True,
2107        **opts,
2108    ) -> Insert:
2109        """
2110        Append to or set the common table expressions.
2111
2112        Example:
2113            >>> insert("SELECT x FROM cte", "t").with_("cte", as_="SELECT * FROM tbl").sql()
2114            'WITH cte AS (SELECT * FROM tbl) INSERT INTO t SELECT x FROM cte'
2115
2116        Args:
2117            alias: the SQL code string to parse as the table name.
2118                If an `Expression` instance is passed, this is used as-is.
2119            as_: the SQL code string to parse as the table expression.
2120                If an `Expression` instance is passed, it will be used as-is.
2121            recursive: set the RECURSIVE part of the expression. Defaults to `False`.
2122            append: if `True`, add to any existing expressions.
2123                Otherwise, this resets the expressions.
2124            dialect: the dialect used to parse the input expression.
2125            copy: if `False`, modify this expression instance in-place.
2126            opts: other options to use to parse the input expressions.
2127
2128        Returns:
2129            The modified expression.
2130        """
2131        return _apply_cte_builder(
2132            self, alias, as_, recursive=recursive, append=append, dialect=dialect, copy=copy, **opts
2133        )
2134
2135
2136class OnConflict(Expression):
2137    arg_types = {
2138        "duplicate": False,
2139        "expressions": False,
2140        "action": False,
2141        "conflict_keys": False,
2142        "constraint": False,
2143    }
2144
2145
2146class Returning(Expression):
2147    arg_types = {"expressions": True, "into": False}
2148
2149
2150# https://dev.mysql.com/doc/refman/8.0/en/charset-introducer.html
2151class Introducer(Expression):
2152    arg_types = {"this": True, "expression": True}
2153
2154
2155# national char, like n'utf8'
2156class National(Expression):
2157    pass
2158
2159
2160class LoadData(Expression):
2161    arg_types = {
2162        "this": True,
2163        "local": False,
2164        "overwrite": False,
2165        "inpath": True,
2166        "partition": False,
2167        "input_format": False,
2168        "serde": False,
2169    }
2170
2171
2172class Partition(Expression):
2173    arg_types = {"expressions": True}
2174
2175
2176class PartitionRange(Expression):
2177    arg_types = {"this": True, "expression": True}
2178
2179
2180class Fetch(Expression):
2181    arg_types = {
2182        "direction": False,
2183        "count": False,
2184        "percent": False,
2185        "with_ties": False,
2186    }
2187
2188
2189class Group(Expression):
2190    arg_types = {
2191        "expressions": False,
2192        "grouping_sets": False,
2193        "cube": False,
2194        "rollup": False,
2195        "totals": False,
2196        "all": False,
2197    }
2198
2199
2200class Lambda(Expression):
2201    arg_types = {"this": True, "expressions": True}
2202
2203
2204class Limit(Expression):
2205    arg_types = {"this": False, "expression": True, "offset": False, "expressions": False}
2206
2207
2208class Literal(Condition):
2209    arg_types = {"this": True, "is_string": True}
2210
2211    @property
2212    def hashable_args(self) -> t.Any:
2213        return (self.this, self.args.get("is_string"))
2214
2215    @classmethod
2216    def number(cls, number) -> Literal:
2217        return cls(this=str(number), is_string=False)
2218
2219    @classmethod
2220    def string(cls, string) -> Literal:
2221        return cls(this=str(string), is_string=True)
2222
2223    @property
2224    def output_name(self) -> str:
2225        return self.name
2226
2227
2228class Join(Expression):
2229    arg_types = {
2230        "this": True,
2231        "on": False,
2232        "side": False,
2233        "kind": False,
2234        "using": False,
2235        "method": False,
2236        "global": False,
2237        "hint": False,
2238        "match_condition": False,  # Snowflake
2239    }
2240
2241    @property
2242    def method(self) -> str:
2243        return self.text("method").upper()
2244
2245    @property
2246    def kind(self) -> str:
2247        return self.text("kind").upper()
2248
2249    @property
2250    def side(self) -> str:
2251        return self.text("side").upper()
2252
2253    @property
2254    def hint(self) -> str:
2255        return self.text("hint").upper()
2256
2257    @property
2258    def alias_or_name(self) -> str:
2259        return self.this.alias_or_name
2260
2261    def on(
2262        self,
2263        *expressions: t.Optional[ExpOrStr],
2264        append: bool = True,
2265        dialect: DialectType = None,
2266        copy: bool = True,
2267        **opts,
2268    ) -> Join:
2269        """
2270        Append to or set the ON expressions.
2271
2272        Example:
2273            >>> import sqlglot
2274            >>> sqlglot.parse_one("JOIN x", into=Join).on("y = 1").sql()
2275            'JOIN x ON y = 1'
2276
2277        Args:
2278            *expressions: the SQL code strings to parse.
2279                If an `Expression` instance is passed, it will be used as-is.
2280                Multiple expressions are combined with an AND operator.
2281            append: if `True`, AND the new expressions to any existing expression.
2282                Otherwise, this resets the expression.
2283            dialect: the dialect used to parse the input expressions.
2284            copy: if `False`, modify this expression instance in-place.
2285            opts: other options to use to parse the input expressions.
2286
2287        Returns:
2288            The modified Join expression.
2289        """
2290        join = _apply_conjunction_builder(
2291            *expressions,
2292            instance=self,
2293            arg="on",
2294            append=append,
2295            dialect=dialect,
2296            copy=copy,
2297            **opts,
2298        )
2299
2300        if join.kind == "CROSS":
2301            join.set("kind", None)
2302
2303        return join
2304
2305    def using(
2306        self,
2307        *expressions: t.Optional[ExpOrStr],
2308        append: bool = True,
2309        dialect: DialectType = None,
2310        copy: bool = True,
2311        **opts,
2312    ) -> Join:
2313        """
2314        Append to or set the USING expressions.
2315
2316        Example:
2317            >>> import sqlglot
2318            >>> sqlglot.parse_one("JOIN x", into=Join).using("foo", "bla").sql()
2319            'JOIN x USING (foo, bla)'
2320
2321        Args:
2322            *expressions: the SQL code strings to parse.
2323                If an `Expression` instance is passed, it will be used as-is.
2324            append: if `True`, concatenate the new expressions to the existing "using" list.
2325                Otherwise, this resets the expression.
2326            dialect: the dialect used to parse the input expressions.
2327            copy: if `False`, modify this expression instance in-place.
2328            opts: other options to use to parse the input expressions.
2329
2330        Returns:
2331            The modified Join expression.
2332        """
2333        join = _apply_list_builder(
2334            *expressions,
2335            instance=self,
2336            arg="using",
2337            append=append,
2338            dialect=dialect,
2339            copy=copy,
2340            **opts,
2341        )
2342
2343        if join.kind == "CROSS":
2344            join.set("kind", None)
2345
2346        return join
2347
2348
2349class Lateral(UDTF):
2350    arg_types = {
2351        "this": True,
2352        "view": False,
2353        "outer": False,
2354        "alias": False,
2355        "cross_apply": False,  # True -> CROSS APPLY, False -> OUTER APPLY
2356    }
2357
2358
2359class MatchRecognizeMeasure(Expression):
2360    arg_types = {
2361        "this": True,
2362        "window_frame": False,
2363    }
2364
2365
2366class MatchRecognize(Expression):
2367    arg_types = {
2368        "partition_by": False,
2369        "order": False,
2370        "measures": False,
2371        "rows": False,
2372        "after": False,
2373        "pattern": False,
2374        "define": False,
2375        "alias": False,
2376    }
2377
2378
2379# Clickhouse FROM FINAL modifier
2380# https://clickhouse.com/docs/en/sql-reference/statements/select/from/#final-modifier
2381class Final(Expression):
2382    pass
2383
2384
2385class Offset(Expression):
2386    arg_types = {"this": False, "expression": True, "expressions": False}
2387
2388
2389class Order(Expression):
2390    arg_types = {
2391        "this": False,
2392        "expressions": True,
2393        "interpolate": False,
2394        "siblings": False,
2395    }
2396
2397
2398# https://clickhouse.com/docs/en/sql-reference/statements/select/order-by#order-by-expr-with-fill-modifier
2399class WithFill(Expression):
2400    arg_types = {"from": False, "to": False, "step": False}
2401
2402
2403# hive specific sorts
2404# https://cwiki.apache.org/confluence/display/Hive/LanguageManual+SortBy
2405class Cluster(Order):
2406    pass
2407
2408
2409class Distribute(Order):
2410    pass
2411
2412
2413class Sort(Order):
2414    pass
2415
2416
2417class Ordered(Expression):
2418    arg_types = {"this": True, "desc": False, "nulls_first": True, "with_fill": False}
2419
2420
2421class Property(Expression):
2422    arg_types = {"this": True, "value": True}
2423
2424
2425class AlgorithmProperty(Property):
2426    arg_types = {"this": True}
2427
2428
2429class AutoIncrementProperty(Property):
2430    arg_types = {"this": True}
2431
2432
2433# https://docs.aws.amazon.com/prescriptive-guidance/latest/materialized-views-redshift/refreshing-materialized-views.html
2434class AutoRefreshProperty(Property):
2435    arg_types = {"this": True}
2436
2437
2438class BackupProperty(Property):
2439    arg_types = {"this": True}
2440
2441
2442class BlockCompressionProperty(Property):
2443    arg_types = {
2444        "autotemp": False,
2445        "always": False,
2446        "default": False,
2447        "manual": False,
2448        "never": False,
2449    }
2450
2451
2452class CharacterSetProperty(Property):
2453    arg_types = {"this": True, "default": True}
2454
2455
2456class ChecksumProperty(Property):
2457    arg_types = {"on": False, "default": False}
2458
2459
2460class CollateProperty(Property):
2461    arg_types = {"this": True, "default": False}
2462
2463
2464class CopyGrantsProperty(Property):
2465    arg_types = {}
2466
2467
2468class DataBlocksizeProperty(Property):
2469    arg_types = {
2470        "size": False,
2471        "units": False,
2472        "minimum": False,
2473        "maximum": False,
2474        "default": False,
2475    }
2476
2477
2478class DefinerProperty(Property):
2479    arg_types = {"this": True}
2480
2481
2482class DistKeyProperty(Property):
2483    arg_types = {"this": True}
2484
2485
2486class DistStyleProperty(Property):
2487    arg_types = {"this": True}
2488
2489
2490class EngineProperty(Property):
2491    arg_types = {"this": True}
2492
2493
2494class HeapProperty(Property):
2495    arg_types = {}
2496
2497
2498class ToTableProperty(Property):
2499    arg_types = {"this": True}
2500
2501
2502class ExecuteAsProperty(Property):
2503    arg_types = {"this": True}
2504
2505
2506class ExternalProperty(Property):
2507    arg_types = {"this": False}
2508
2509
2510class FallbackProperty(Property):
2511    arg_types = {"no": True, "protection": False}
2512
2513
2514class FileFormatProperty(Property):
2515    arg_types = {"this": True}
2516
2517
2518class FreespaceProperty(Property):
2519    arg_types = {"this": True, "percent": False}
2520
2521
2522class GlobalProperty(Property):
2523    arg_types = {}
2524
2525
2526class IcebergProperty(Property):
2527    arg_types = {}
2528
2529
2530class InheritsProperty(Property):
2531    arg_types = {"expressions": True}
2532
2533
2534class InputModelProperty(Property):
2535    arg_types = {"this": True}
2536
2537
2538class OutputModelProperty(Property):
2539    arg_types = {"this": True}
2540
2541
2542class IsolatedLoadingProperty(Property):
2543    arg_types = {"no": False, "concurrent": False, "target": False}
2544
2545
2546class JournalProperty(Property):
2547    arg_types = {
2548        "no": False,
2549        "dual": False,
2550        "before": False,
2551        "local": False,
2552        "after": False,
2553    }
2554
2555
2556class LanguageProperty(Property):
2557    arg_types = {"this": True}
2558
2559
2560# spark ddl
2561class ClusteredByProperty(Property):
2562    arg_types = {"expressions": True, "sorted_by": False, "buckets": True}
2563
2564
2565class DictProperty(Property):
2566    arg_types = {"this": True, "kind": True, "settings": False}
2567
2568
2569class DictSubProperty(Property):
2570    pass
2571
2572
2573class DictRange(Property):
2574    arg_types = {"this": True, "min": True, "max": True}
2575
2576
2577# Clickhouse CREATE ... ON CLUSTER modifier
2578# https://clickhouse.com/docs/en/sql-reference/distributed-ddl
2579class OnCluster(Property):
2580    arg_types = {"this": True}
2581
2582
2583class LikeProperty(Property):
2584    arg_types = {"this": True, "expressions": False}
2585
2586
2587class LocationProperty(Property):
2588    arg_types = {"this": True}
2589
2590
2591class LockProperty(Property):
2592    arg_types = {"this": True}
2593
2594
2595class LockingProperty(Property):
2596    arg_types = {
2597        "this": False,
2598        "kind": True,
2599        "for_or_in": False,
2600        "lock_type": True,
2601        "override": False,
2602    }
2603
2604
2605class LogProperty(Property):
2606    arg_types = {"no": True}
2607
2608
2609class MaterializedProperty(Property):
2610    arg_types = {"this": False}
2611
2612
2613class MergeBlockRatioProperty(Property):
2614    arg_types = {"this": False, "no": False, "default": False, "percent": False}
2615
2616
2617class NoPrimaryIndexProperty(Property):
2618    arg_types = {}
2619
2620
2621class OnProperty(Property):
2622    arg_types = {"this": True}
2623
2624
2625class OnCommitProperty(Property):
2626    arg_types = {"delete": False}
2627
2628
2629class PartitionedByProperty(Property):
2630    arg_types = {"this": True}
2631
2632
2633# https://www.postgresql.org/docs/current/sql-createtable.html
2634class PartitionBoundSpec(Expression):
2635    # this -> IN / MODULUS, expression -> REMAINDER, from_expressions -> FROM (...), to_expressions -> TO (...)
2636    arg_types = {
2637        "this": False,
2638        "expression": False,
2639        "from_expressions": False,
2640        "to_expressions": False,
2641    }
2642
2643
2644class PartitionedOfProperty(Property):
2645    # this -> parent_table (schema), expression -> FOR VALUES ... / DEFAULT
2646    arg_types = {"this": True, "expression": True}
2647
2648
2649class RemoteWithConnectionModelProperty(Property):
2650    arg_types = {"this": True}
2651
2652
2653class ReturnsProperty(Property):
2654    arg_types = {"this": True, "is_table": False, "table": False}
2655
2656
2657class RowFormatProperty(Property):
2658    arg_types = {"this": True}
2659
2660
2661class RowFormatDelimitedProperty(Property):
2662    # https://cwiki.apache.org/confluence/display/hive/languagemanual+dml
2663    arg_types = {
2664        "fields": False,
2665        "escaped": False,
2666        "collection_items": False,
2667        "map_keys": False,
2668        "lines": False,
2669        "null": False,
2670        "serde": False,
2671    }
2672
2673
2674class RowFormatSerdeProperty(Property):
2675    arg_types = {"this": True, "serde_properties": False}
2676
2677
2678# https://spark.apache.org/docs/3.1.2/sql-ref-syntax-qry-select-transform.html
2679class QueryTransform(Expression):
2680    arg_types = {
2681        "expressions": True,
2682        "command_script": True,
2683        "schema": False,
2684        "row_format_before": False,
2685        "record_writer": False,
2686        "row_format_after": False,
2687        "record_reader": False,
2688    }
2689
2690
2691class SampleProperty(Property):
2692    arg_types = {"this": True}
2693
2694
2695class SchemaCommentProperty(Property):
2696    arg_types = {"this": True}
2697
2698
2699class SerdeProperties(Property):
2700    arg_types = {"expressions": True}
2701
2702
2703class SetProperty(Property):
2704    arg_types = {"multi": True}
2705
2706
2707class SharingProperty(Property):
2708    arg_types = {"this": False}
2709
2710
2711class SetConfigProperty(Property):
2712    arg_types = {"this": True}
2713
2714
2715class SettingsProperty(Property):
2716    arg_types = {"expressions": True}
2717
2718
2719class SortKeyProperty(Property):
2720    arg_types = {"this": True, "compound": False}
2721
2722
2723class SqlReadWriteProperty(Property):
2724    arg_types = {"this": True}
2725
2726
2727class SqlSecurityProperty(Property):
2728    arg_types = {"definer": True}
2729
2730
2731class StabilityProperty(Property):
2732    arg_types = {"this": True}
2733
2734
2735class TemporaryProperty(Property):
2736    arg_types = {"this": False}
2737
2738
2739class TransformModelProperty(Property):
2740    arg_types = {"expressions": True}
2741
2742
2743class TransientProperty(Property):
2744    arg_types = {"this": False}
2745
2746
2747class UnloggedProperty(Property):
2748    arg_types = {}
2749
2750
2751# https://learn.microsoft.com/en-us/sql/t-sql/statements/create-view-transact-sql?view=sql-server-ver16
2752class ViewAttributeProperty(Property):
2753    arg_types = {"this": True}
2754
2755
2756class VolatileProperty(Property):
2757    arg_types = {"this": False}
2758
2759
2760class WithDataProperty(Property):
2761    arg_types = {"no": True, "statistics": False}
2762
2763
2764class WithJournalTableProperty(Property):
2765    arg_types = {"this": True}
2766
2767
2768class WithSystemVersioningProperty(Property):
2769    # this -> history table name, expression -> data consistency check
2770    arg_types = {"this": False, "expression": False}
2771
2772
2773class Properties(Expression):
2774    arg_types = {"expressions": True}
2775
2776    NAME_TO_PROPERTY = {
2777        "ALGORITHM": AlgorithmProperty,
2778        "AUTO_INCREMENT": AutoIncrementProperty,
2779        "CHARACTER SET": CharacterSetProperty,
2780        "CLUSTERED_BY": ClusteredByProperty,
2781        "COLLATE": CollateProperty,
2782        "COMMENT": SchemaCommentProperty,
2783        "DEFINER": DefinerProperty,
2784        "DISTKEY": DistKeyProperty,
2785        "DISTSTYLE": DistStyleProperty,
2786        "ENGINE": EngineProperty,
2787        "EXECUTE AS": ExecuteAsProperty,
2788        "FORMAT": FileFormatProperty,
2789        "LANGUAGE": LanguageProperty,
2790        "LOCATION": LocationProperty,
2791        "LOCK": LockProperty,
2792        "PARTITIONED_BY": PartitionedByProperty,
2793        "RETURNS": ReturnsProperty,
2794        "ROW_FORMAT": RowFormatProperty,
2795        "SORTKEY": SortKeyProperty,
2796    }
2797
2798    PROPERTY_TO_NAME = {v: k for k, v in NAME_TO_PROPERTY.items()}
2799
2800    # CREATE property locations
2801    # Form: schema specified
2802    #   create [POST_CREATE]
2803    #     table a [POST_NAME]
2804    #     (b int) [POST_SCHEMA]
2805    #     with ([POST_WITH])
2806    #     index (b) [POST_INDEX]
2807    #
2808    # Form: alias selection
2809    #   create [POST_CREATE]
2810    #     table a [POST_NAME]
2811    #     as [POST_ALIAS] (select * from b) [POST_EXPRESSION]
2812    #     index (c) [POST_INDEX]
2813    class Location(AutoName):
2814        POST_CREATE = auto()
2815        POST_NAME = auto()
2816        POST_SCHEMA = auto()
2817        POST_WITH = auto()
2818        POST_ALIAS = auto()
2819        POST_EXPRESSION = auto()
2820        POST_INDEX = auto()
2821        UNSUPPORTED = auto()
2822
2823    @classmethod
2824    def from_dict(cls, properties_dict: t.Dict) -> Properties:
2825        expressions = []
2826        for key, value in properties_dict.items():
2827            property_cls = cls.NAME_TO_PROPERTY.get(key.upper())
2828            if property_cls:
2829                expressions.append(property_cls(this=convert(value)))
2830            else:
2831                expressions.append(Property(this=Literal.string(key), value=convert(value)))
2832
2833        return cls(expressions=expressions)
2834
2835
2836class Qualify(Expression):
2837    pass
2838
2839
2840class InputOutputFormat(Expression):
2841    arg_types = {"input_format": False, "output_format": False}
2842
2843
2844# https://www.ibm.com/docs/en/ias?topic=procedures-return-statement-in-sql
2845class Return(Expression):
2846    pass
2847
2848
2849class Reference(Expression):
2850    arg_types = {"this": True, "expressions": False, "options": False}
2851
2852
2853class Tuple(Expression):
2854    arg_types = {"expressions": False}
2855
2856    def isin(
2857        self,
2858        *expressions: t.Any,
2859        query: t.Optional[ExpOrStr] = None,
2860        unnest: t.Optional[ExpOrStr] | t.Collection[ExpOrStr] = None,
2861        copy: bool = True,
2862        **opts,
2863    ) -> In:
2864        return In(
2865            this=maybe_copy(self, copy),
2866            expressions=[convert(e, copy=copy) for e in expressions],
2867            query=maybe_parse(query, copy=copy, **opts) if query else None,
2868            unnest=(
2869                Unnest(
2870                    expressions=[
2871                        maybe_parse(t.cast(ExpOrStr, e), copy=copy, **opts)
2872                        for e in ensure_list(unnest)
2873                    ]
2874                )
2875                if unnest
2876                else None
2877            ),
2878        )
2879
2880
2881QUERY_MODIFIERS = {
2882    "match": False,
2883    "laterals": False,
2884    "joins": False,
2885    "connect": False,
2886    "pivots": False,
2887    "prewhere": False,
2888    "where": False,
2889    "group": False,
2890    "having": False,
2891    "qualify": False,
2892    "windows": False,
2893    "distribute": False,
2894    "sort": False,
2895    "cluster": False,
2896    "order": False,
2897    "limit": False,
2898    "offset": False,
2899    "locks": False,
2900    "sample": False,
2901    "settings": False,
2902    "format": False,
2903    "options": False,
2904}
2905
2906
2907# https://learn.microsoft.com/en-us/sql/t-sql/queries/option-clause-transact-sql?view=sql-server-ver16
2908# https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-query?view=sql-server-ver16
2909class QueryOption(Expression):
2910    arg_types = {"this": True, "expression": False}
2911
2912
2913# https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-ver16
2914class WithTableHint(Expression):
2915    arg_types = {"expressions": True}
2916
2917
2918# https://dev.mysql.com/doc/refman/8.0/en/index-hints.html
2919class IndexTableHint(Expression):
2920    arg_types = {"this": True, "expressions": False, "target": False}
2921
2922
2923# https://docs.snowflake.com/en/sql-reference/constructs/at-before
2924class HistoricalData(Expression):
2925    arg_types = {"this": True, "kind": True, "expression": True}
2926
2927
2928class Table(Expression):
2929    arg_types = {
2930        "this": False,
2931        "alias": False,
2932        "db": False,
2933        "catalog": False,
2934        "laterals": False,
2935        "joins": False,
2936        "pivots": False,
2937        "hints": False,
2938        "system_time": False,
2939        "version": False,
2940        "format": False,
2941        "pattern": False,
2942        "ordinality": False,
2943        "when": False,
2944        "only": False,
2945        "partition": False,
2946    }
2947
2948    @property
2949    def name(self) -> str:
2950        if isinstance(self.this, Func):
2951            return ""
2952        return self.this.name
2953
2954    @property
2955    def db(self) -> str:
2956        return self.text("db")
2957
2958    @property
2959    def catalog(self) -> str:
2960        return self.text("catalog")
2961
2962    @property
2963    def selects(self) -> t.List[Expression]:
2964        return []
2965
2966    @property
2967    def named_selects(self) -> t.List[str]:
2968        return []
2969
2970    @property
2971    def parts(self) -> t.List[Expression]:
2972        """Return the parts of a table in order catalog, db, table."""
2973        parts: t.List[Expression] = []
2974
2975        for arg in ("catalog", "db", "this"):
2976            part = self.args.get(arg)
2977
2978            if isinstance(part, Dot):
2979                parts.extend(part.flatten())
2980            elif isinstance(part, Expression):
2981                parts.append(part)
2982
2983        return parts
2984
2985    def to_column(self, copy: bool = True) -> Alias | Column | Dot:
2986        parts = self.parts
2987        col = column(*reversed(parts[0:4]), fields=parts[4:], copy=copy)  # type: ignore
2988        alias = self.args.get("alias")
2989        if alias:
2990            col = alias_(col, alias.this, copy=copy)
2991        return col
2992
2993
2994class Union(Query):
2995    arg_types = {
2996        "with": False,
2997        "this": True,
2998        "expression": True,
2999        "distinct": False,
3000        "by_name": False,
3001        **QUERY_MODIFIERS,
3002    }
3003
3004    def select(
3005        self,
3006        *expressions: t.Optional[ExpOrStr],
3007        append: bool = True,
3008        dialect: DialectType = None,
3009        copy: bool = True,
3010        **opts,
3011    ) -> Union:
3012        this = maybe_copy(self, copy)
3013        this.this.unnest().select(*expressions, append=append, dialect=dialect, copy=False, **opts)
3014        this.expression.unnest().select(
3015            *expressions, append=append, dialect=dialect, copy=False, **opts
3016        )
3017        return this
3018
3019    @property
3020    def named_selects(self) -> t.List[str]:
3021        return self.this.unnest().named_selects
3022
3023    @property
3024    def is_star(self) -> bool:
3025        return self.this.is_star or self.expression.is_star
3026
3027    @property
3028    def selects(self) -> t.List[Expression]:
3029        return self.this.unnest().selects
3030
3031    @property
3032    def left(self) -> Expression:
3033        return self.this
3034
3035    @property
3036    def right(self) -> Expression:
3037        return self.expression
3038
3039
3040class Except(Union):
3041    pass
3042
3043
3044class Intersect(Union):
3045    pass
3046
3047
3048class Unnest(UDTF):
3049    arg_types = {
3050        "expressions": True,
3051        "alias": False,
3052        "offset": False,
3053    }
3054
3055    @property
3056    def selects(self) -> t.List[Expression]:
3057        columns = super().selects
3058        offset = self.args.get("offset")
3059        if offset:
3060            columns = columns + [to_identifier("offset") if offset is True else offset]
3061        return columns
3062
3063
3064class Update(Expression):
3065    arg_types = {
3066        "with": False,
3067        "this": False,
3068        "expressions": True,
3069        "from": False,
3070        "where": False,
3071        "returning": False,
3072        "order": False,
3073        "limit": False,
3074    }
3075
3076
3077class Values(UDTF):
3078    arg_types = {"expressions": True, "alias": False}
3079
3080
3081class Var(Expression):
3082    pass
3083
3084
3085class Version(Expression):
3086    """
3087    Time travel, iceberg, bigquery etc
3088    https://trino.io/docs/current/connector/iceberg.html?highlight=snapshot#using-snapshots
3089    https://www.databricks.com/blog/2019/02/04/introducing-delta-time-travel-for-large-scale-data-lakes.html
3090    https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#for_system_time_as_of
3091    https://learn.microsoft.com/en-us/sql/relational-databases/tables/querying-data-in-a-system-versioned-temporal-table?view=sql-server-ver16
3092    this is either TIMESTAMP or VERSION
3093    kind is ("AS OF", "BETWEEN")
3094    """
3095
3096    arg_types = {"this": True, "kind": True, "expression": False}
3097
3098
3099class Schema(Expression):
3100    arg_types = {"this": False, "expressions": False}
3101
3102
3103# https://dev.mysql.com/doc/refman/8.0/en/select.html
3104# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/SELECT.html
3105class Lock(Expression):
3106    arg_types = {"update": True, "expressions": False, "wait": False}
3107
3108
3109class Select(Query):
3110    arg_types = {
3111        "with": False,
3112        "kind": False,
3113        "expressions": False,
3114        "hint": False,
3115        "distinct": False,
3116        "into": False,
3117        "from": False,
3118        **QUERY_MODIFIERS,
3119    }
3120
3121    def from_(
3122        self, expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts
3123    ) -> Select:
3124        """
3125        Set the FROM expression.
3126
3127        Example:
3128            >>> Select().from_("tbl").select("x").sql()
3129            'SELECT x FROM tbl'
3130
3131        Args:
3132            expression : the SQL code strings to parse.
3133                If a `From` instance is passed, this is used as-is.
3134                If another `Expression` instance is passed, it will be wrapped in a `From`.
3135            dialect: the dialect used to parse the input expression.
3136            copy: if `False`, modify this expression instance in-place.
3137            opts: other options to use to parse the input expressions.
3138
3139        Returns:
3140            The modified Select expression.
3141        """
3142        return _apply_builder(
3143            expression=expression,
3144            instance=self,
3145            arg="from",
3146            into=From,
3147            prefix="FROM",
3148            dialect=dialect,
3149            copy=copy,
3150            **opts,
3151        )
3152
3153    def group_by(
3154        self,
3155        *expressions: t.Optional[ExpOrStr],
3156        append: bool = True,
3157        dialect: DialectType = None,
3158        copy: bool = True,
3159        **opts,
3160    ) -> Select:
3161        """
3162        Set the GROUP BY expression.
3163
3164        Example:
3165            >>> Select().from_("tbl").select("x", "COUNT(1)").group_by("x").sql()
3166            'SELECT x, COUNT(1) FROM tbl GROUP BY x'
3167
3168        Args:
3169            *expressions: the SQL code strings to parse.
3170                If a `Group` instance is passed, this is used as-is.
3171                If another `Expression` instance is passed, it will be wrapped in a `Group`.
3172                If nothing is passed in then a group by is not applied to the expression
3173            append: if `True`, add to any existing expressions.
3174                Otherwise, this flattens all the `Group` expression into a single expression.
3175            dialect: the dialect used to parse the input expression.
3176            copy: if `False`, modify this expression instance in-place.
3177            opts: other options to use to parse the input expressions.
3178
3179        Returns:
3180            The modified Select expression.
3181        """
3182        if not expressions:
3183            return self if not copy else self.copy()
3184
3185        return _apply_child_list_builder(
3186            *expressions,
3187            instance=self,
3188            arg="group",
3189            append=append,
3190            copy=copy,
3191            prefix="GROUP BY",
3192            into=Group,
3193            dialect=dialect,
3194            **opts,
3195        )
3196
3197    def sort_by(
3198        self,
3199        *expressions: t.Optional[ExpOrStr],
3200        append: bool = True,
3201        dialect: DialectType = None,
3202        copy: bool = True,
3203        **opts,
3204    ) -> Select:
3205        """
3206        Set the SORT BY expression.
3207
3208        Example:
3209            >>> Select().from_("tbl").select("x").sort_by("x DESC").sql(dialect="hive")
3210            'SELECT x FROM tbl SORT BY x DESC'
3211
3212        Args:
3213            *expressions: the SQL code strings to parse.
3214                If a `Group` instance is passed, this is used as-is.
3215                If another `Expression` instance is passed, it will be wrapped in a `SORT`.
3216            append: if `True`, add to any existing expressions.
3217                Otherwise, this flattens all the `Order` expression into a single expression.
3218            dialect: the dialect used to parse the input expression.
3219            copy: if `False`, modify this expression instance in-place.
3220            opts: other options to use to parse the input expressions.
3221
3222        Returns:
3223            The modified Select expression.
3224        """
3225        return _apply_child_list_builder(
3226            *expressions,
3227            instance=self,
3228            arg="sort",
3229            append=append,
3230            copy=copy,
3231            prefix="SORT BY",
3232            into=Sort,
3233            dialect=dialect,
3234            **opts,
3235        )
3236
3237    def cluster_by(
3238        self,
3239        *expressions: t.Optional[ExpOrStr],
3240        append: bool = True,
3241        dialect: DialectType = None,
3242        copy: bool = True,
3243        **opts,
3244    ) -> Select:
3245        """
3246        Set the CLUSTER BY expression.
3247
3248        Example:
3249            >>> Select().from_("tbl").select("x").cluster_by("x DESC").sql(dialect="hive")
3250            'SELECT x FROM tbl CLUSTER BY x DESC'
3251
3252        Args:
3253            *expressions: the SQL code strings to parse.
3254                If a `Group` instance is passed, this is used as-is.
3255                If another `Expression` instance is passed, it will be wrapped in a `Cluster`.
3256            append: if `True`, add to any existing expressions.
3257                Otherwise, this flattens all the `Order` expression into a single expression.
3258            dialect: the dialect used to parse the input expression.
3259            copy: if `False`, modify this expression instance in-place.
3260            opts: other options to use to parse the input expressions.
3261
3262        Returns:
3263            The modified Select expression.
3264        """
3265        return _apply_child_list_builder(
3266            *expressions,
3267            instance=self,
3268            arg="cluster",
3269            append=append,
3270            copy=copy,
3271            prefix="CLUSTER BY",
3272            into=Cluster,
3273            dialect=dialect,
3274            **opts,
3275        )
3276
3277    def select(
3278        self,
3279        *expressions: t.Optional[ExpOrStr],
3280        append: bool = True,
3281        dialect: DialectType = None,
3282        copy: bool = True,
3283        **opts,
3284    ) -> Select:
3285        return _apply_list_builder(
3286            *expressions,
3287            instance=self,
3288            arg="expressions",
3289            append=append,
3290            dialect=dialect,
3291            into=Expression,
3292            copy=copy,
3293            **opts,
3294        )
3295
3296    def lateral(
3297        self,
3298        *expressions: t.Optional[ExpOrStr],
3299        append: bool = True,
3300        dialect: DialectType = None,
3301        copy: bool = True,
3302        **opts,
3303    ) -> Select:
3304        """
3305        Append to or set the LATERAL expressions.
3306
3307        Example:
3308            >>> Select().select("x").lateral("OUTER explode(y) tbl2 AS z").from_("tbl").sql()
3309            'SELECT x FROM tbl LATERAL VIEW OUTER EXPLODE(y) tbl2 AS z'
3310
3311        Args:
3312            *expressions: the SQL code strings to parse.
3313                If an `Expression` instance is passed, it will be used as-is.
3314            append: if `True`, add to any existing expressions.
3315                Otherwise, this resets the expressions.
3316            dialect: the dialect used to parse the input expressions.
3317            copy: if `False`, modify this expression instance in-place.
3318            opts: other options to use to parse the input expressions.
3319
3320        Returns:
3321            The modified Select expression.
3322        """
3323        return _apply_list_builder(
3324            *expressions,
3325            instance=self,
3326            arg="laterals",
3327            append=append,
3328            into=Lateral,
3329            prefix="LATERAL VIEW",
3330            dialect=dialect,
3331            copy=copy,
3332            **opts,
3333        )
3334
3335    def join(
3336        self,
3337        expression: ExpOrStr,
3338        on: t.Optional[ExpOrStr] = None,
3339        using: t.Optional[ExpOrStr | t.Collection[ExpOrStr]] = None,
3340        append: bool = True,
3341        join_type: t.Optional[str] = None,
3342        join_alias: t.Optional[Identifier | str] = None,
3343        dialect: DialectType = None,
3344        copy: bool = True,
3345        **opts,
3346    ) -> Select:
3347        """
3348        Append to or set the JOIN expressions.
3349
3350        Example:
3351            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y").sql()
3352            'SELECT * FROM tbl JOIN tbl2 ON tbl1.y = tbl2.y'
3353
3354            >>> Select().select("1").from_("a").join("b", using=["x", "y", "z"]).sql()
3355            'SELECT 1 FROM a JOIN b USING (x, y, z)'
3356
3357            Use `join_type` to change the type of join:
3358
3359            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y", join_type="left outer").sql()
3360            'SELECT * FROM tbl LEFT OUTER JOIN tbl2 ON tbl1.y = tbl2.y'
3361
3362        Args:
3363            expression: the SQL code string to parse.
3364                If an `Expression` instance is passed, it will be used as-is.
3365            on: optionally specify the join "on" criteria as a SQL string.
3366                If an `Expression` instance is passed, it will be used as-is.
3367            using: optionally specify the join "using" criteria as a SQL string.
3368                If an `Expression` instance is passed, it will be used as-is.
3369            append: if `True`, add to any existing expressions.
3370                Otherwise, this resets the expressions.
3371            join_type: if set, alter the parsed join type.
3372            join_alias: an optional alias for the joined source.
3373            dialect: the dialect used to parse the input expressions.
3374            copy: if `False`, modify this expression instance in-place.
3375            opts: other options to use to parse the input expressions.
3376
3377        Returns:
3378            Select: the modified expression.
3379        """
3380        parse_args: t.Dict[str, t.Any] = {"dialect": dialect, **opts}
3381
3382        try:
3383            expression = maybe_parse(expression, into=Join, prefix="JOIN", **parse_args)
3384        except ParseError:
3385            expression = maybe_parse(expression, into=(Join, Expression), **parse_args)
3386
3387        join = expression if isinstance(expression, Join) else Join(this=expression)
3388
3389        if isinstance(join.this, Select):
3390            join.this.replace(join.this.subquery())
3391
3392        if join_type:
3393            method: t.Optional[Token]
3394            side: t.Optional[Token]
3395            kind: t.Optional[Token]
3396
3397            method, side, kind = maybe_parse(join_type, into="JOIN_TYPE", **parse_args)  # type: ignore
3398
3399            if method:
3400                join.set("method", method.text)
3401            if side:
3402                join.set("side", side.text)
3403            if kind:
3404                join.set("kind", kind.text)
3405
3406        if on:
3407            on = and_(*ensure_list(on), dialect=dialect, copy=copy, **opts)
3408            join.set("on", on)
3409
3410        if using:
3411            join = _apply_list_builder(
3412                *ensure_list(using),
3413                instance=join,
3414                arg="using",
3415                append=append,
3416                copy=copy,
3417                into=Identifier,
3418                **opts,
3419            )
3420
3421        if join_alias:
3422            join.set("this", alias_(join.this, join_alias, table=True))
3423
3424        return _apply_list_builder(
3425            join,
3426            instance=self,
3427            arg="joins",
3428            append=append,
3429            copy=copy,
3430            **opts,
3431        )
3432
3433    def where(
3434        self,
3435        *expressions: t.Optional[ExpOrStr],
3436        append: bool = True,
3437        dialect: DialectType = None,
3438        copy: bool = True,
3439        **opts,
3440    ) -> Select:
3441        """
3442        Append to or set the WHERE expressions.
3443
3444        Example:
3445            >>> Select().select("x").from_("tbl").where("x = 'a' OR x < 'b'").sql()
3446            "SELECT x FROM tbl WHERE x = 'a' OR x < 'b'"
3447
3448        Args:
3449            *expressions: the SQL code strings to parse.
3450                If an `Expression` instance is passed, it will be used as-is.
3451                Multiple expressions are combined with an AND operator.
3452            append: if `True`, AND the new expressions to any existing expression.
3453                Otherwise, this resets the expression.
3454            dialect: the dialect used to parse the input expressions.
3455            copy: if `False`, modify this expression instance in-place.
3456            opts: other options to use to parse the input expressions.
3457
3458        Returns:
3459            Select: the modified expression.
3460        """
3461        return _apply_conjunction_builder(
3462            *expressions,
3463            instance=self,
3464            arg="where",
3465            append=append,
3466            into=Where,
3467            dialect=dialect,
3468            copy=copy,
3469            **opts,
3470        )
3471
3472    def having(
3473        self,
3474        *expressions: t.Optional[ExpOrStr],
3475        append: bool = True,
3476        dialect: DialectType = None,
3477        copy: bool = True,
3478        **opts,
3479    ) -> Select:
3480        """
3481        Append to or set the HAVING expressions.
3482
3483        Example:
3484            >>> Select().select("x", "COUNT(y)").from_("tbl").group_by("x").having("COUNT(y) > 3").sql()
3485            'SELECT x, COUNT(y) FROM tbl GROUP BY x HAVING COUNT(y) > 3'
3486
3487        Args:
3488            *expressions: the SQL code strings to parse.
3489                If an `Expression` instance is passed, it will be used as-is.
3490                Multiple expressions are combined with an AND operator.
3491            append: if `True`, AND the new expressions to any existing expression.
3492                Otherwise, this resets the expression.
3493            dialect: the dialect used to parse the input expressions.
3494            copy: if `False`, modify this expression instance in-place.
3495            opts: other options to use to parse the input expressions.
3496
3497        Returns:
3498            The modified Select expression.
3499        """
3500        return _apply_conjunction_builder(
3501            *expressions,
3502            instance=self,
3503            arg="having",
3504            append=append,
3505            into=Having,
3506            dialect=dialect,
3507            copy=copy,
3508            **opts,
3509        )
3510
3511    def window(
3512        self,
3513        *expressions: t.Optional[ExpOrStr],
3514        append: bool = True,
3515        dialect: DialectType = None,
3516        copy: bool = True,
3517        **opts,
3518    ) -> Select:
3519        return _apply_list_builder(
3520            *expressions,
3521            instance=self,
3522            arg="windows",
3523            append=append,
3524            into=Window,
3525            dialect=dialect,
3526            copy=copy,
3527            **opts,
3528        )
3529
3530    def qualify(
3531        self,
3532        *expressions: t.Optional[ExpOrStr],
3533        append: bool = True,
3534        dialect: DialectType = None,
3535        copy: bool = True,
3536        **opts,
3537    ) -> Select:
3538        return _apply_conjunction_builder(
3539            *expressions,
3540            instance=self,
3541            arg="qualify",
3542            append=append,
3543            into=Qualify,
3544            dialect=dialect,
3545            copy=copy,
3546            **opts,
3547        )
3548
3549    def distinct(
3550        self, *ons: t.Optional[ExpOrStr], distinct: bool = True, copy: bool = True
3551    ) -> Select:
3552        """
3553        Set the OFFSET expression.
3554
3555        Example:
3556            >>> Select().from_("tbl").select("x").distinct().sql()
3557            'SELECT DISTINCT x FROM tbl'
3558
3559        Args:
3560            ons: the expressions to distinct on
3561            distinct: whether the Select should be distinct
3562            copy: if `False`, modify this expression instance in-place.
3563
3564        Returns:
3565            Select: the modified expression.
3566        """
3567        instance = maybe_copy(self, copy)
3568        on = Tuple(expressions=[maybe_parse(on, copy=copy) for on in ons if on]) if ons else None
3569        instance.set("distinct", Distinct(on=on) if distinct else None)
3570        return instance
3571
3572    def ctas(
3573        self,
3574        table: ExpOrStr,
3575        properties: t.Optional[t.Dict] = None,
3576        dialect: DialectType = None,
3577        copy: bool = True,
3578        **opts,
3579    ) -> Create:
3580        """
3581        Convert this expression to a CREATE TABLE AS statement.
3582
3583        Example:
3584            >>> Select().select("*").from_("tbl").ctas("x").sql()
3585            'CREATE TABLE x AS SELECT * FROM tbl'
3586
3587        Args:
3588            table: the SQL code string to parse as the table name.
3589                If another `Expression` instance is passed, it will be used as-is.
3590            properties: an optional mapping of table properties
3591            dialect: the dialect used to parse the input table.
3592            copy: if `False`, modify this expression instance in-place.
3593            opts: other options to use to parse the input table.
3594
3595        Returns:
3596            The new Create expression.
3597        """
3598        instance = maybe_copy(self, copy)
3599        table_expression = maybe_parse(table, into=Table, dialect=dialect, **opts)
3600
3601        properties_expression = None
3602        if properties:
3603            properties_expression = Properties.from_dict(properties)
3604
3605        return Create(
3606            this=table_expression,
3607            kind="TABLE",
3608            expression=instance,
3609            properties=properties_expression,
3610        )
3611
3612    def lock(self, update: bool = True, copy: bool = True) -> Select:
3613        """
3614        Set the locking read mode for this expression.
3615
3616        Examples:
3617            >>> Select().select("x").from_("tbl").where("x = 'a'").lock().sql("mysql")
3618            "SELECT x FROM tbl WHERE x = 'a' FOR UPDATE"
3619
3620            >>> Select().select("x").from_("tbl").where("x = 'a'").lock(update=False).sql("mysql")
3621            "SELECT x FROM tbl WHERE x = 'a' FOR SHARE"
3622
3623        Args:
3624            update: if `True`, the locking type will be `FOR UPDATE`, else it will be `FOR SHARE`.
3625            copy: if `False`, modify this expression instance in-place.
3626
3627        Returns:
3628            The modified expression.
3629        """
3630        inst = maybe_copy(self, copy)
3631        inst.set("locks", [Lock(update=update)])
3632
3633        return inst
3634
3635    def hint(self, *hints: ExpOrStr, dialect: DialectType = None, copy: bool = True) -> Select:
3636        """
3637        Set hints for this expression.
3638
3639        Examples:
3640            >>> Select().select("x").from_("tbl").hint("BROADCAST(y)").sql(dialect="spark")
3641            'SELECT /*+ BROADCAST(y) */ x FROM tbl'
3642
3643        Args:
3644            hints: The SQL code strings to parse as the hints.
3645                If an `Expression` instance is passed, it will be used as-is.
3646            dialect: The dialect used to parse the hints.
3647            copy: If `False`, modify this expression instance in-place.
3648
3649        Returns:
3650            The modified expression.
3651        """
3652        inst = maybe_copy(self, copy)
3653        inst.set(
3654            "hint", Hint(expressions=[maybe_parse(h, copy=copy, dialect=dialect) for h in hints])
3655        )
3656
3657        return inst
3658
3659    @property
3660    def named_selects(self) -> t.List[str]:
3661        return [e.output_name for e in self.expressions if e.alias_or_name]
3662
3663    @property
3664    def is_star(self) -> bool:
3665        return any(expression.is_star for expression in self.expressions)
3666
3667    @property
3668    def selects(self) -> t.List[Expression]:
3669        return self.expressions
3670
3671
3672UNWRAPPED_QUERIES = (Select, Union)
3673
3674
3675class Subquery(DerivedTable, Query):
3676    arg_types = {
3677        "this": True,
3678        "alias": False,
3679        "with": False,
3680        **QUERY_MODIFIERS,
3681    }
3682
3683    def unnest(self):
3684        """Returns the first non subquery."""
3685        expression = self
3686        while isinstance(expression, Subquery):
3687            expression = expression.this
3688        return expression
3689
3690    def unwrap(self) -> Subquery:
3691        expression = self
3692        while expression.same_parent and expression.is_wrapper:
3693            expression = t.cast(Subquery, expression.parent)
3694        return expression
3695
3696    def select(
3697        self,
3698        *expressions: t.Optional[ExpOrStr],
3699        append: bool = True,
3700        dialect: DialectType = None,
3701        copy: bool = True,
3702        **opts,
3703    ) -> Subquery:
3704        this = maybe_copy(self, copy)
3705        this.unnest().select(*expressions, append=append, dialect=dialect, copy=False, **opts)
3706        return this
3707
3708    @property
3709    def is_wrapper(self) -> bool:
3710        """
3711        Whether this Subquery acts as a simple wrapper around another expression.
3712
3713        SELECT * FROM (((SELECT * FROM t)))
3714                      ^
3715                      This corresponds to a "wrapper" Subquery node
3716        """
3717        return all(v is None for k, v in self.args.items() if k != "this")
3718
3719    @property
3720    def is_star(self) -> bool:
3721        return self.this.is_star
3722
3723    @property
3724    def output_name(self) -> str:
3725        return self.alias
3726
3727
3728class TableSample(Expression):
3729    arg_types = {
3730        "this": False,
3731        "expressions": False,
3732        "method": False,
3733        "bucket_numerator": False,
3734        "bucket_denominator": False,
3735        "bucket_field": False,
3736        "percent": False,
3737        "rows": False,
3738        "size": False,
3739        "seed": False,
3740    }
3741
3742
3743class Tag(Expression):
3744    """Tags are used for generating arbitrary sql like SELECT <span>x</span>."""
3745
3746    arg_types = {
3747        "this": False,
3748        "prefix": False,
3749        "postfix": False,
3750    }
3751
3752
3753# Represents both the standard SQL PIVOT operator and DuckDB's "simplified" PIVOT syntax
3754# https://duckdb.org/docs/sql/statements/pivot
3755class Pivot(Expression):
3756    arg_types = {
3757        "this": False,
3758        "alias": False,
3759        "expressions": False,
3760        "field": False,
3761        "unpivot": False,
3762        "using": False,
3763        "group": False,
3764        "columns": False,
3765        "include_nulls": False,
3766    }
3767
3768    @property
3769    def unpivot(self) -> bool:
3770        return bool(self.args.get("unpivot"))
3771
3772
3773class Window(Condition):
3774    arg_types = {
3775        "this": True,
3776        "partition_by": False,
3777        "order": False,
3778        "spec": False,
3779        "alias": False,
3780        "over": False,
3781        "first": False,
3782    }
3783
3784
3785class WindowSpec(Expression):
3786    arg_types = {
3787        "kind": False,
3788        "start": False,
3789        "start_side": False,
3790        "end": False,
3791        "end_side": False,
3792    }
3793
3794
3795class PreWhere(Expression):
3796    pass
3797
3798
3799class Where(Expression):
3800    pass
3801
3802
3803class Star(Expression):
3804    arg_types = {"except": False, "replace": False}
3805
3806    @property
3807    def name(self) -> str:
3808        return "*"
3809
3810    @property
3811    def output_name(self) -> str:
3812        return self.name
3813
3814
3815class Parameter(Condition):
3816    arg_types = {"this": True, "expression": False}
3817
3818
3819class SessionParameter(Condition):
3820    arg_types = {"this": True, "kind": False}
3821
3822
3823class Placeholder(Condition):
3824    arg_types = {"this": False, "kind": False}
3825
3826    @property
3827    def name(self) -> str:
3828        return self.this or "?"
3829
3830
3831class Null(Condition):
3832    arg_types: t.Dict[str, t.Any] = {}
3833
3834    @property
3835    def name(self) -> str:
3836        return "NULL"
3837
3838
3839class Boolean(Condition):
3840    pass
3841
3842
3843class DataTypeParam(Expression):
3844    arg_types = {"this": True, "expression": False}
3845
3846    @property
3847    def name(self) -> str:
3848        return self.this.name
3849
3850
3851class DataType(Expression):
3852    arg_types = {
3853        "this": True,
3854        "expressions": False,
3855        "nested": False,
3856        "values": False,
3857        "prefix": False,
3858        "kind": False,
3859    }
3860
3861    class Type(AutoName):
3862        ARRAY = auto()
3863        AGGREGATEFUNCTION = auto()
3864        SIMPLEAGGREGATEFUNCTION = auto()
3865        BIGDECIMAL = auto()
3866        BIGINT = auto()
3867        BIGSERIAL = auto()
3868        BINARY = auto()
3869        BIT = auto()
3870        BOOLEAN = auto()
3871        BPCHAR = auto()
3872        CHAR = auto()
3873        DATE = auto()
3874        DATE32 = auto()
3875        DATEMULTIRANGE = auto()
3876        DATERANGE = auto()
3877        DATETIME = auto()
3878        DATETIME64 = auto()
3879        DECIMAL = auto()
3880        DOUBLE = auto()
3881        ENUM = auto()
3882        ENUM8 = auto()
3883        ENUM16 = auto()
3884        FIXEDSTRING = auto()
3885        FLOAT = auto()
3886        GEOGRAPHY = auto()
3887        GEOMETRY = auto()
3888        HLLSKETCH = auto()
3889        HSTORE = auto()
3890        IMAGE = auto()
3891        INET = auto()
3892        INT = auto()
3893        INT128 = auto()
3894        INT256 = auto()
3895        INT4MULTIRANGE = auto()
3896        INT4RANGE = auto()
3897        INT8MULTIRANGE = auto()
3898        INT8RANGE = auto()
3899        INTERVAL = auto()
3900        IPADDRESS = auto()
3901        IPPREFIX = auto()
3902        IPV4 = auto()
3903        IPV6 = auto()
3904        JSON = auto()
3905        JSONB = auto()
3906        LONGBLOB = auto()
3907        LONGTEXT = auto()
3908        LOWCARDINALITY = auto()
3909        MAP = auto()
3910        MEDIUMBLOB = auto()
3911        MEDIUMINT = auto()
3912        MEDIUMTEXT = auto()
3913        MONEY = auto()
3914        NAME = auto()
3915        NCHAR = auto()
3916        NESTED = auto()
3917        NULL = auto()
3918        NULLABLE = auto()
3919        NUMMULTIRANGE = auto()
3920        NUMRANGE = auto()
3921        NVARCHAR = auto()
3922        OBJECT = auto()
3923        ROWVERSION = auto()
3924        SERIAL = auto()
3925        SET = auto()
3926        SMALLINT = auto()
3927        SMALLMONEY = auto()
3928        SMALLSERIAL = auto()
3929        STRUCT = auto()
3930        SUPER = auto()
3931        TEXT = auto()
3932        TINYBLOB = auto()
3933        TINYTEXT = auto()
3934        TIME = auto()
3935        TIMETZ = auto()
3936        TIMESTAMP = auto()
3937        TIMESTAMPNTZ = auto()
3938        TIMESTAMPLTZ = auto()
3939        TIMESTAMPTZ = auto()
3940        TIMESTAMP_S = auto()
3941        TIMESTAMP_MS = auto()
3942        TIMESTAMP_NS = auto()
3943        TINYINT = auto()
3944        TSMULTIRANGE = auto()
3945        TSRANGE = auto()
3946        TSTZMULTIRANGE = auto()
3947        TSTZRANGE = auto()
3948        UBIGINT = auto()
3949        UINT = auto()
3950        UINT128 = auto()
3951        UINT256 = auto()
3952        UMEDIUMINT = auto()
3953        UDECIMAL = auto()
3954        UNIQUEIDENTIFIER = auto()
3955        UNKNOWN = auto()  # Sentinel value, useful for type annotation
3956        USERDEFINED = "USER-DEFINED"
3957        USMALLINT = auto()
3958        UTINYINT = auto()
3959        UUID = auto()
3960        VARBINARY = auto()
3961        VARCHAR = auto()
3962        VARIANT = auto()
3963        XML = auto()
3964        YEAR = auto()
3965        TDIGEST = auto()
3966
3967    STRUCT_TYPES = {
3968        Type.NESTED,
3969        Type.OBJECT,
3970        Type.STRUCT,
3971    }
3972
3973    NESTED_TYPES = {
3974        *STRUCT_TYPES,
3975        Type.ARRAY,
3976        Type.MAP,
3977    }
3978
3979    TEXT_TYPES = {
3980        Type.CHAR,
3981        Type.NCHAR,
3982        Type.NVARCHAR,
3983        Type.TEXT,
3984        Type.VARCHAR,
3985        Type.NAME,
3986    }
3987
3988    SIGNED_INTEGER_TYPES = {
3989        Type.BIGINT,
3990        Type.INT,
3991        Type.INT128,
3992        Type.INT256,
3993        Type.MEDIUMINT,
3994        Type.SMALLINT,
3995        Type.TINYINT,
3996    }
3997
3998    UNSIGNED_INTEGER_TYPES = {
3999        Type.UBIGINT,
4000        Type.UINT,
4001        Type.UINT128,
4002        Type.UINT256,
4003        Type.UMEDIUMINT,
4004        Type.USMALLINT,
4005        Type.UTINYINT,
4006    }
4007
4008    INTEGER_TYPES = {
4009        *SIGNED_INTEGER_TYPES,
4010        *UNSIGNED_INTEGER_TYPES,
4011        Type.BIT,
4012    }
4013
4014    FLOAT_TYPES = {
4015        Type.DOUBLE,
4016        Type.FLOAT,
4017    }
4018
4019    REAL_TYPES = {
4020        *FLOAT_TYPES,
4021        Type.BIGDECIMAL,
4022        Type.DECIMAL,
4023        Type.MONEY,
4024        Type.SMALLMONEY,
4025        Type.UDECIMAL,
4026    }
4027
4028    NUMERIC_TYPES = {
4029        *INTEGER_TYPES,
4030        *REAL_TYPES,
4031    }
4032
4033    TEMPORAL_TYPES = {
4034        Type.DATE,
4035        Type.DATE32,
4036        Type.DATETIME,
4037        Type.DATETIME64,
4038        Type.TIME,
4039        Type.TIMESTAMP,
4040        Type.TIMESTAMPNTZ,
4041        Type.TIMESTAMPLTZ,
4042        Type.TIMESTAMPTZ,
4043        Type.TIMESTAMP_MS,
4044        Type.TIMESTAMP_NS,
4045        Type.TIMESTAMP_S,
4046        Type.TIMETZ,
4047    }
4048
4049    @classmethod
4050    def build(
4051        cls,
4052        dtype: DATA_TYPE,
4053        dialect: DialectType = None,
4054        udt: bool = False,
4055        copy: bool = True,
4056        **kwargs,
4057    ) -> DataType:
4058        """
4059        Constructs a DataType object.
4060
4061        Args:
4062            dtype: the data type of interest.
4063            dialect: the dialect to use for parsing `dtype`, in case it's a string.
4064            udt: when set to True, `dtype` will be used as-is if it can't be parsed into a
4065                DataType, thus creating a user-defined type.
4066            copy: whether to copy the data type.
4067            kwargs: additional arguments to pass in the constructor of DataType.
4068
4069        Returns:
4070            The constructed DataType object.
4071        """
4072        from sqlglot import parse_one
4073
4074        if isinstance(dtype, str):
4075            if dtype.upper() == "UNKNOWN":
4076                return DataType(this=DataType.Type.UNKNOWN, **kwargs)
4077
4078            try:
4079                data_type_exp = parse_one(
4080                    dtype, read=dialect, into=DataType, error_level=ErrorLevel.IGNORE
4081                )
4082            except ParseError:
4083                if udt:
4084                    return DataType(this=DataType.Type.USERDEFINED, kind=dtype, **kwargs)
4085                raise
4086        elif isinstance(dtype, DataType.Type):
4087            data_type_exp = DataType(this=dtype)
4088        elif isinstance(dtype, DataType):
4089            return maybe_copy(dtype, copy)
4090        else:
4091            raise ValueError(f"Invalid data type: {type(dtype)}. Expected str or DataType.Type")
4092
4093        return DataType(**{**data_type_exp.args, **kwargs})
4094
4095    def is_type(self, *dtypes: DATA_TYPE) -> bool:
4096        """
4097        Checks whether this DataType matches one of the provided data types. Nested types or precision
4098        will be compared using "structural equivalence" semantics, so e.g. array<int> != array<float>.
4099
4100        Args:
4101            dtypes: the data types to compare this DataType to.
4102
4103        Returns:
4104            True, if and only if there is a type in `dtypes` which is equal to this DataType.
4105        """
4106        for dtype in dtypes:
4107            other = DataType.build(dtype, copy=False, udt=True)
4108
4109            if (
4110                other.expressions
4111                or self.this == DataType.Type.USERDEFINED
4112                or other.this == DataType.Type.USERDEFINED
4113            ):
4114                matches = self == other
4115            else:
4116                matches = self.this == other.this
4117
4118            if matches:
4119                return True
4120        return False
4121
4122
4123DATA_TYPE = t.Union[str, DataType, DataType.Type]
4124
4125
4126# https://www.postgresql.org/docs/15/datatype-pseudo.html
4127class PseudoType(DataType):
4128    arg_types = {"this": True}
4129
4130
4131# https://www.postgresql.org/docs/15/datatype-oid.html
4132class ObjectIdentifier(DataType):
4133    arg_types = {"this": True}
4134
4135
4136# WHERE x <OP> EXISTS|ALL|ANY|SOME(SELECT ...)
4137class SubqueryPredicate(Predicate):
4138    pass
4139
4140
4141class All(SubqueryPredicate):
4142    pass
4143
4144
4145class Any(SubqueryPredicate):
4146    pass
4147
4148
4149class Exists(SubqueryPredicate):
4150    pass
4151
4152
4153# Commands to interact with the databases or engines. For most of the command
4154# expressions we parse whatever comes after the command's name as a string.
4155class Command(Expression):
4156    arg_types = {"this": True, "expression": False}
4157
4158
4159class Transaction(Expression):
4160    arg_types = {"this": False, "modes": False, "mark": False}
4161
4162
4163class Commit(Expression):
4164    arg_types = {"chain": False, "this": False, "durability": False}
4165
4166
4167class Rollback(Expression):
4168    arg_types = {"savepoint": False, "this": False}
4169
4170
4171class AlterTable(Expression):
4172    arg_types = {
4173        "this": True,
4174        "actions": True,
4175        "exists": False,
4176        "only": False,
4177        "options": False,
4178    }
4179
4180
4181class AddConstraint(Expression):
4182    arg_types = {"expressions": True}
4183
4184
4185class DropPartition(Expression):
4186    arg_types = {"expressions": True, "exists": False}
4187
4188
4189# Binary expressions like (ADD a b)
4190class Binary(Condition):
4191    arg_types = {"this": True, "expression": True}
4192
4193    @property
4194    def left(self) -> Expression:
4195        return self.this
4196
4197    @property
4198    def right(self) -> Expression:
4199        return self.expression
4200
4201
4202class Add(Binary):
4203    pass
4204
4205
4206class Connector(Binary):
4207    pass
4208
4209
4210class And(Connector):
4211    pass
4212
4213
4214class Or(Connector):
4215    pass
4216
4217
4218class BitwiseAnd(Binary):
4219    pass
4220
4221
4222class BitwiseLeftShift(Binary):
4223    pass
4224
4225
4226class BitwiseOr(Binary):
4227    pass
4228
4229
4230class BitwiseRightShift(Binary):
4231    pass
4232
4233
4234class BitwiseXor(Binary):
4235    pass
4236
4237
4238class Div(Binary):
4239    arg_types = {"this": True, "expression": True, "typed": False, "safe": False}
4240
4241
4242class Overlaps(Binary):
4243    pass
4244
4245
4246class Dot(Binary):
4247    @property
4248    def is_star(self) -> bool:
4249        return self.expression.is_star
4250
4251    @property
4252    def name(self) -> str:
4253        return self.expression.name
4254
4255    @property
4256    def output_name(self) -> str:
4257        return self.name
4258
4259    @classmethod
4260    def build(self, expressions: t.Sequence[Expression]) -> Dot:
4261        """Build a Dot object with a sequence of expressions."""
4262        if len(expressions) < 2:
4263            raise ValueError("Dot requires >= 2 expressions.")
4264
4265        return t.cast(Dot, reduce(lambda x, y: Dot(this=x, expression=y), expressions))
4266
4267    @property
4268    def parts(self) -> t.List[Expression]:
4269        """Return the parts of a table / column in order catalog, db, table."""
4270        this, *parts = self.flatten()
4271
4272        parts.reverse()
4273
4274        for arg in COLUMN_PARTS:
4275            part = this.args.get(arg)
4276
4277            if isinstance(part, Expression):
4278                parts.append(part)
4279
4280        parts.reverse()
4281        return parts
4282
4283
4284class DPipe(Binary):
4285    arg_types = {"this": True, "expression": True, "safe": False}
4286
4287
4288class EQ(Binary, Predicate):
4289    pass
4290
4291
4292class NullSafeEQ(Binary, Predicate):
4293    pass
4294
4295
4296class NullSafeNEQ(Binary, Predicate):
4297    pass
4298
4299
4300# Represents e.g. := in DuckDB which is mostly used for setting parameters
4301class PropertyEQ(Binary):
4302    pass
4303
4304
4305class Distance(Binary):
4306    pass
4307
4308
4309class Escape(Binary):
4310    pass
4311
4312
4313class Glob(Binary, Predicate):
4314    pass
4315
4316
4317class GT(Binary, Predicate):
4318    pass
4319
4320
4321class GTE(Binary, Predicate):
4322    pass
4323
4324
4325class ILike(Binary, Predicate):
4326    pass
4327
4328
4329class ILikeAny(Binary, Predicate):
4330    pass
4331
4332
4333class IntDiv(Binary):
4334    pass
4335
4336
4337class Is(Binary, Predicate):
4338    pass
4339
4340
4341class Kwarg(Binary):
4342    """Kwarg in special functions like func(kwarg => y)."""
4343
4344
4345class Like(Binary, Predicate):
4346    pass
4347
4348
4349class LikeAny(Binary, Predicate):
4350    pass
4351
4352
4353class LT(Binary, Predicate):
4354    pass
4355
4356
4357class LTE(Binary, Predicate):
4358    pass
4359
4360
4361class Mod(Binary):
4362    pass
4363
4364
4365class Mul(Binary):
4366    pass
4367
4368
4369class NEQ(Binary, Predicate):
4370    pass
4371
4372
4373# https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PATH
4374class Operator(Binary):
4375    arg_types = {"this": True, "operator": True, "expression": True}
4376
4377
4378class SimilarTo(Binary, Predicate):
4379    pass
4380
4381
4382class Slice(Binary):
4383    arg_types = {"this": False, "expression": False}
4384
4385
4386class Sub(Binary):
4387    pass
4388
4389
4390# Unary Expressions
4391# (NOT a)
4392class Unary(Condition):
4393    pass
4394
4395
4396class BitwiseNot(Unary):
4397    pass
4398
4399
4400class Not(Unary):
4401    pass
4402
4403
4404class Paren(Unary):
4405    @property
4406    def output_name(self) -> str:
4407        return self.this.name
4408
4409
4410class Neg(Unary):
4411    pass
4412
4413
4414class Alias(Expression):
4415    arg_types = {"this": True, "alias": False}
4416
4417    @property
4418    def output_name(self) -> str:
4419        return self.alias
4420
4421
4422# BigQuery requires the UNPIVOT column list aliases to be either strings or ints, but
4423# other dialects require identifiers. This enables us to transpile between them easily.
4424class PivotAlias(Alias):
4425    pass
4426
4427
4428class Aliases(Expression):
4429    arg_types = {"this": True, "expressions": True}
4430
4431    @property
4432    def aliases(self):
4433        return self.expressions
4434
4435
4436# https://docs.aws.amazon.com/redshift/latest/dg/query-super.html
4437class AtIndex(Expression):
4438    arg_types = {"this": True, "expression": True}
4439
4440
4441class AtTimeZone(Expression):
4442    arg_types = {"this": True, "zone": True}
4443
4444
4445class FromTimeZone(Expression):
4446    arg_types = {"this": True, "zone": True}
4447
4448
4449class Between(Predicate):
4450    arg_types = {"this": True, "low": True, "high": True}
4451
4452
4453class Bracket(Condition):
4454    # https://cloud.google.com/bigquery/docs/reference/standard-sql/operators#array_subscript_operator
4455    arg_types = {
4456        "this": True,
4457        "expressions": True,
4458        "offset": False,
4459        "safe": False,
4460        "returns_list_for_maps": False,
4461    }
4462
4463    @property
4464    def output_name(self) -> str:
4465        if len(self.expressions) == 1:
4466            return self.expressions[0].output_name
4467
4468        return super().output_name
4469
4470
4471class Distinct(Expression):
4472    arg_types = {"expressions": False, "on": False}
4473
4474
4475class In(Predicate):
4476    arg_types = {
4477        "this": True,
4478        "expressions": False,
4479        "query": False,
4480        "unnest": False,
4481        "field": False,
4482        "is_global": False,
4483    }
4484
4485
4486# https://cloud.google.com/bigquery/docs/reference/standard-sql/procedural-language#for-in
4487class ForIn(Expression):
4488    arg_types = {"this": True, "expression": True}
4489
4490
4491class TimeUnit(Expression):
4492    """Automatically converts unit arg into a var."""
4493
4494    arg_types = {"unit": False}
4495
4496    UNABBREVIATED_UNIT_NAME = {
4497        "D": "DAY",
4498        "H": "HOUR",
4499        "M": "MINUTE",
4500        "MS": "MILLISECOND",
4501        "NS": "NANOSECOND",
4502        "Q": "QUARTER",
4503        "S": "SECOND",
4504        "US": "MICROSECOND",
4505        "W": "WEEK",
4506        "Y": "YEAR",
4507    }
4508
4509    VAR_LIKE = (Column, Literal, Var)
4510
4511    def __init__(self, **args):
4512        unit = args.get("unit")
4513        if isinstance(unit, self.VAR_LIKE):
4514            args["unit"] = Var(
4515                this=(self.UNABBREVIATED_UNIT_NAME.get(unit.name) or unit.name).upper()
4516            )
4517        elif isinstance(unit, Week):
4518            unit.set("this", Var(this=unit.this.name.upper()))
4519
4520        super().__init__(**args)
4521
4522    @property
4523    def unit(self) -> t.Optional[Var | IntervalSpan]:
4524        return self.args.get("unit")
4525
4526
4527class IntervalOp(TimeUnit):
4528    arg_types = {"unit": True, "expression": True}
4529
4530    def interval(self):
4531        return Interval(
4532            this=self.expression.copy(),
4533            unit=self.unit.copy(),
4534        )
4535
4536
4537# https://www.oracletutorial.com/oracle-basics/oracle-interval/
4538# https://trino.io/docs/current/language/types.html#interval-day-to-second
4539# https://docs.databricks.com/en/sql/language-manual/data-types/interval-type.html
4540class IntervalSpan(DataType):
4541    arg_types = {"this": True, "expression": True}
4542
4543
4544class Interval(TimeUnit):
4545    arg_types = {"this": False, "unit": False}
4546
4547
4548class IgnoreNulls(Expression):
4549    pass
4550
4551
4552class RespectNulls(Expression):
4553    pass
4554
4555
4556# https://cloud.google.com/bigquery/docs/reference/standard-sql/aggregate-function-calls#max_min_clause
4557class HavingMax(Expression):
4558    arg_types = {"this": True, "expression": True, "max": True}
4559
4560
4561# Functions
4562class Func(Condition):
4563    """
4564    The base class for all function expressions.
4565
4566    Attributes:
4567        is_var_len_args (bool): if set to True the last argument defined in arg_types will be
4568            treated as a variable length argument and the argument's value will be stored as a list.
4569        _sql_names (list): the SQL name (1st item in the list) and aliases (subsequent items) for this
4570            function expression. These values are used to map this node to a name during parsing as
4571            well as to provide the function's name during SQL string generation. By default the SQL
4572            name is set to the expression's class name transformed to snake case.
4573    """
4574
4575    is_var_len_args = False
4576
4577    @classmethod
4578    def from_arg_list(cls, args):
4579        if cls.is_var_len_args:
4580            all_arg_keys = list(cls.arg_types)
4581            # If this function supports variable length argument treat the last argument as such.
4582            non_var_len_arg_keys = all_arg_keys[:-1] if cls.is_var_len_args else all_arg_keys
4583            num_non_var = len(non_var_len_arg_keys)
4584
4585            args_dict = {arg_key: arg for arg, arg_key in zip(args, non_var_len_arg_keys)}
4586            args_dict[all_arg_keys[-1]] = args[num_non_var:]
4587        else:
4588            args_dict = {arg_key: arg for arg, arg_key in zip(args, cls.arg_types)}
4589
4590        return cls(**args_dict)
4591
4592    @classmethod
4593    def sql_names(cls):
4594        if cls is Func:
4595            raise NotImplementedError(
4596                "SQL name is only supported by concrete function implementations"
4597            )
4598        if "_sql_names" not in cls.__dict__:
4599            cls._sql_names = [camel_to_snake_case(cls.__name__)]
4600        return cls._sql_names
4601
4602    @classmethod
4603    def sql_name(cls):
4604        return cls.sql_names()[0]
4605
4606    @classmethod
4607    def default_parser_mappings(cls):
4608        return {name: cls.from_arg_list for name in cls.sql_names()}
4609
4610
4611class AggFunc(Func):
4612    pass
4613
4614
4615class ParameterizedAgg(AggFunc):
4616    arg_types = {"this": True, "expressions": True, "params": True}
4617
4618
4619class Abs(Func):
4620    pass
4621
4622
4623class ArgMax(AggFunc):
4624    arg_types = {"this": True, "expression": True, "count": False}
4625    _sql_names = ["ARG_MAX", "ARGMAX", "MAX_BY"]
4626
4627
4628class ArgMin(AggFunc):
4629    arg_types = {"this": True, "expression": True, "count": False}
4630    _sql_names = ["ARG_MIN", "ARGMIN", "MIN_BY"]
4631
4632
4633class ApproxTopK(AggFunc):
4634    arg_types = {"this": True, "expression": False, "counters": False}
4635
4636
4637class Flatten(Func):
4638    pass
4639
4640
4641# https://spark.apache.org/docs/latest/api/sql/index.html#transform
4642class Transform(Func):
4643    arg_types = {"this": True, "expression": True}
4644
4645
4646class Anonymous(Func):
4647    arg_types = {"this": True, "expressions": False}
4648    is_var_len_args = True
4649
4650    @property
4651    def name(self) -> str:
4652        return self.this if isinstance(self.this, str) else self.this.name
4653
4654
4655class AnonymousAggFunc(AggFunc):
4656    arg_types = {"this": True, "expressions": False}
4657    is_var_len_args = True
4658
4659
4660# https://clickhouse.com/docs/en/sql-reference/aggregate-functions/combinators
4661class CombinedAggFunc(AnonymousAggFunc):
4662    arg_types = {"this": True, "expressions": False, "parts": True}
4663
4664
4665class CombinedParameterizedAgg(ParameterizedAgg):
4666    arg_types = {"this": True, "expressions": True, "params": True, "parts": True}
4667
4668
4669# https://docs.snowflake.com/en/sql-reference/functions/hll
4670# https://docs.aws.amazon.com/redshift/latest/dg/r_HLL_function.html
4671class Hll(AggFunc):
4672    arg_types = {"this": True, "expressions": False}
4673    is_var_len_args = True
4674
4675
4676class ApproxDistinct(AggFunc):
4677    arg_types = {"this": True, "accuracy": False}
4678    _sql_names = ["APPROX_DISTINCT", "APPROX_COUNT_DISTINCT"]
4679
4680
4681class Array(Func):
4682    arg_types = {"expressions": False}
4683    is_var_len_args = True
4684
4685
4686# https://docs.snowflake.com/en/sql-reference/functions/to_array
4687class ToArray(Func):
4688    pass
4689
4690
4691# https://docs.snowflake.com/en/sql-reference/functions/to_char
4692# https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/TO_CHAR-number.html
4693class ToChar(Func):
4694    arg_types = {"this": True, "format": False, "nlsparam": False}
4695
4696
4697# https://docs.snowflake.com/en/sql-reference/functions/to_decimal
4698# https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/TO_NUMBER.html
4699class ToNumber(Func):
4700    arg_types = {
4701        "this": True,
4702        "format": False,
4703        "nlsparam": False,
4704        "precision": False,
4705        "scale": False,
4706    }
4707
4708
4709# https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver16#syntax
4710class Convert(Func):
4711    arg_types = {"this": True, "expression": True, "style": False}
4712
4713
4714class GenerateSeries(Func):
4715    arg_types = {"start": True, "end": True, "step": False, "is_end_exclusive": False}
4716
4717
4718class ArrayAgg(AggFunc):
4719    pass
4720
4721
4722class ArrayUniqueAgg(AggFunc):
4723    pass
4724
4725
4726class ArrayAll(Func):
4727    arg_types = {"this": True, "expression": True}
4728
4729
4730# Represents Python's `any(f(x) for x in array)`, where `array` is `this` and `f` is `expression`
4731class ArrayAny(Func):
4732    arg_types = {"this": True, "expression": True}
4733
4734
4735class ArrayConcat(Func):
4736    _sql_names = ["ARRAY_CONCAT", "ARRAY_CAT"]
4737    arg_types = {"this": True, "expressions": False}
4738    is_var_len_args = True
4739
4740
4741class ArrayContains(Binary, Func):
4742    pass
4743
4744
4745class ArrayContained(Binary):
4746    pass
4747
4748
4749class ArrayFilter(Func):
4750    arg_types = {"this": True, "expression": True}
4751    _sql_names = ["FILTER", "ARRAY_FILTER"]
4752
4753
4754class ArrayToString(Func):
4755    arg_types = {"this": True, "expression": True, "null": False}
4756    _sql_names = ["ARRAY_TO_STRING", "ARRAY_JOIN"]
4757
4758
4759class ArrayOverlaps(Binary, Func):
4760    pass
4761
4762
4763class ArraySize(Func):
4764    arg_types = {"this": True, "expression": False}
4765    _sql_names = ["ARRAY_SIZE", "ARRAY_LENGTH"]
4766
4767
4768class ArraySort(Func):
4769    arg_types = {"this": True, "expression": False}
4770
4771
4772class ArraySum(Func):
4773    arg_types = {"this": True, "expression": False}
4774
4775
4776class ArrayUnionAgg(AggFunc):
4777    pass
4778
4779
4780class Avg(AggFunc):
4781    pass
4782
4783
4784class AnyValue(AggFunc):
4785    pass
4786
4787
4788class Lag(AggFunc):
4789    arg_types = {"this": True, "offset": False, "default": False}
4790
4791
4792class Lead(AggFunc):
4793    arg_types = {"this": True, "offset": False, "default": False}
4794
4795
4796# some dialects have a distinction between first and first_value, usually first is an aggregate func
4797# and first_value is a window func
4798class First(AggFunc):
4799    pass
4800
4801
4802class Last(AggFunc):
4803    pass
4804
4805
4806class FirstValue(AggFunc):
4807    pass
4808
4809
4810class LastValue(AggFunc):
4811    pass
4812
4813
4814class NthValue(AggFunc):
4815    arg_types = {"this": True, "offset": True}
4816
4817
4818class Case(Func):
4819    arg_types = {"this": False, "ifs": True, "default": False}
4820
4821    def when(self, condition: ExpOrStr, then: ExpOrStr, copy: bool = True, **opts) -> Case:
4822        instance = maybe_copy(self, copy)
4823        instance.append(
4824            "ifs",
4825            If(
4826                this=maybe_parse(condition, copy=copy, **opts),
4827                true=maybe_parse(then, copy=copy, **opts),
4828            ),
4829        )
4830        return instance
4831
4832    def else_(self, condition: ExpOrStr, copy: bool = True, **opts) -> Case:
4833        instance = maybe_copy(self, copy)
4834        instance.set("default", maybe_parse(condition, copy=copy, **opts))
4835        return instance
4836
4837
4838class Cast(Func):
4839    arg_types = {
4840        "this": True,
4841        "to": True,
4842        "format": False,
4843        "safe": False,
4844        "action": False,
4845    }
4846
4847    @property
4848    def name(self) -> str:
4849        return self.this.name
4850
4851    @property
4852    def to(self) -> DataType:
4853        return self.args["to"]
4854
4855    @property
4856    def output_name(self) -> str:
4857        return self.name
4858
4859    def is_type(self, *dtypes: DATA_TYPE) -> bool:
4860        """
4861        Checks whether this Cast's DataType matches one of the provided data types. Nested types
4862        like arrays or structs will be compared using "structural equivalence" semantics, so e.g.
4863        array<int> != array<float>.
4864
4865        Args:
4866            dtypes: the data types to compare this Cast's DataType to.
4867
4868        Returns:
4869            True, if and only if there is a type in `dtypes` which is equal to this Cast's DataType.
4870        """
4871        return self.to.is_type(*dtypes)
4872
4873
4874class TryCast(Cast):
4875    pass
4876
4877
4878class Try(Func):
4879    pass
4880
4881
4882class CastToStrType(Func):
4883    arg_types = {"this": True, "to": True}
4884
4885
4886class Collate(Binary, Func):
4887    pass
4888
4889
4890class Ceil(Func):
4891    arg_types = {"this": True, "decimals": False}
4892    _sql_names = ["CEIL", "CEILING"]
4893
4894
4895class Coalesce(Func):
4896    arg_types = {"this": True, "expressions": False}
4897    is_var_len_args = True
4898    _sql_names = ["COALESCE", "IFNULL", "NVL"]
4899
4900
4901class Chr(Func):
4902    arg_types = {"this": True, "charset": False, "expressions": False}
4903    is_var_len_args = True
4904    _sql_names = ["CHR", "CHAR"]
4905
4906
4907class Concat(Func):
4908    arg_types = {"expressions": True, "safe": False, "coalesce": False}
4909    is_var_len_args = True
4910
4911
4912class ConcatWs(Concat):
4913    _sql_names = ["CONCAT_WS"]
4914
4915
4916# https://docs.oracle.com/cd/B13789_01/server.101/b10759/operators004.htm#i1035022
4917class ConnectByRoot(Func):
4918    pass
4919
4920
4921class Count(AggFunc):
4922    arg_types = {"this": False, "expressions": False}
4923    is_var_len_args = True
4924
4925
4926class CountIf(AggFunc):
4927    _sql_names = ["COUNT_IF", "COUNTIF"]
4928
4929
4930# cube root
4931class Cbrt(Func):
4932    pass
4933
4934
4935class CurrentDate(Func):
4936    arg_types = {"this": False}
4937
4938
4939class CurrentDatetime(Func):
4940    arg_types = {"this": False}
4941
4942
4943class CurrentTime(Func):
4944    arg_types = {"this": False}
4945
4946
4947class CurrentTimestamp(Func):
4948    arg_types = {"this": False, "transaction": False}
4949
4950
4951class CurrentUser(Func):
4952    arg_types = {"this": False}
4953
4954
4955class DateAdd(Func, IntervalOp):
4956    arg_types = {"this": True, "expression": True, "unit": False}
4957
4958
4959class DateSub(Func, IntervalOp):
4960    arg_types = {"this": True, "expression": True, "unit": False}
4961
4962
4963class DateDiff(Func, TimeUnit):
4964    _sql_names = ["DATEDIFF", "DATE_DIFF"]
4965    arg_types = {"this": True, "expression": True, "unit": False}
4966
4967
4968class DateTrunc(Func):
4969    arg_types = {"unit": True, "this": True, "zone": False}
4970
4971    def __init__(self, **args):
4972        unit = args.get("unit")
4973        if isinstance(unit, TimeUnit.VAR_LIKE):
4974            args["unit"] = Literal.string(
4975                (TimeUnit.UNABBREVIATED_UNIT_NAME.get(unit.name) or unit.name).upper()
4976            )
4977        elif isinstance(unit, Week):
4978            unit.set("this", Literal.string(unit.this.name.upper()))
4979
4980        super().__init__(**args)
4981
4982    @property
4983    def unit(self) -> Expression:
4984        return self.args["unit"]
4985
4986
4987class DatetimeAdd(Func, IntervalOp):
4988    arg_types = {"this": True, "expression": True, "unit": False}
4989
4990
4991class DatetimeSub(Func, IntervalOp):
4992    arg_types = {"this": True, "expression": True, "unit": False}
4993
4994
4995class DatetimeDiff(Func, TimeUnit):
4996    arg_types = {"this": True, "expression": True, "unit": False}
4997
4998
4999class DatetimeTrunc(Func, TimeUnit):
5000    arg_types = {"this": True, "unit": True, "zone": False}
5001
5002
5003class DayOfWeek(Func):
5004    _sql_names = ["DAY_OF_WEEK", "DAYOFWEEK"]
5005
5006
5007class DayOfMonth(Func):
5008    _sql_names = ["DAY_OF_MONTH", "DAYOFMONTH"]
5009
5010
5011class DayOfYear(Func):
5012    _sql_names = ["DAY_OF_YEAR", "DAYOFYEAR"]
5013
5014
5015class ToDays(Func):
5016    pass
5017
5018
5019class WeekOfYear(Func):
5020    _sql_names = ["WEEK_OF_YEAR", "WEEKOFYEAR"]
5021
5022
5023class MonthsBetween(Func):
5024    arg_types = {"this": True, "expression": True, "roundoff": False}
5025
5026
5027class LastDay(Func, TimeUnit):
5028    _sql_names = ["LAST_DAY", "LAST_DAY_OF_MONTH"]
5029    arg_types = {"this": True, "unit": False}
5030
5031
5032class Extract(Func):
5033    arg_types = {"this": True, "expression": True}
5034
5035
5036class Timestamp(Func):
5037    arg_types = {"this": False, "expression": False, "with_tz": False}
5038
5039
5040class TimestampAdd(Func, TimeUnit):
5041    arg_types = {"this": True, "expression": True, "unit": False}
5042
5043
5044class TimestampSub(Func, TimeUnit):
5045    arg_types = {"this": True, "expression": True, "unit": False}
5046
5047
5048class TimestampDiff(Func, TimeUnit):
5049    _sql_names = ["TIMESTAMPDIFF", "TIMESTAMP_DIFF"]
5050    arg_types = {"this": True, "expression": True, "unit": False}
5051
5052
5053class TimestampTrunc(Func, TimeUnit):
5054    arg_types = {"this": True, "unit": True, "zone": False}
5055
5056
5057class TimeAdd(Func, TimeUnit):
5058    arg_types = {"this": True, "expression": True, "unit": False}
5059
5060
5061class TimeSub(Func, TimeUnit):
5062    arg_types = {"this": True, "expression": True, "unit": False}
5063
5064
5065class TimeDiff(Func, TimeUnit):
5066    arg_types = {"this": True, "expression": True, "unit": False}
5067
5068
5069class TimeTrunc(Func, TimeUnit):
5070    arg_types = {"this": True, "unit": True, "zone": False}
5071
5072
5073class DateFromParts(Func):
5074    _sql_names = ["DATE_FROM_PARTS", "DATEFROMPARTS"]
5075    arg_types = {"year": True, "month": True, "day": True}
5076
5077
5078class TimeFromParts(Func):
5079    _sql_names = ["TIME_FROM_PARTS", "TIMEFROMPARTS"]
5080    arg_types = {
5081        "hour": True,
5082        "min": True,
5083        "sec": True,
5084        "nano": False,
5085        "fractions": False,
5086        "precision": False,
5087    }
5088
5089
5090class DateStrToDate(Func):
5091    pass
5092
5093
5094class DateToDateStr(Func):
5095    pass
5096
5097
5098class DateToDi(Func):
5099    pass
5100
5101
5102# https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#date
5103class Date(Func):
5104    arg_types = {"this": False, "zone": False, "expressions": False}
5105    is_var_len_args = True
5106
5107
5108class Day(Func):
5109    pass
5110
5111
5112class Decode(Func):
5113    arg_types = {"this": True, "charset": True, "replace": False}
5114
5115
5116class DiToDate(Func):
5117    pass
5118
5119
5120class Encode(Func):
5121    arg_types = {"this": True, "charset": True}
5122
5123
5124class Exp(Func):
5125    pass
5126
5127
5128# https://docs.snowflake.com/en/sql-reference/functions/flatten
5129class Explode(Func):
5130    arg_types = {"this": True, "expressions": False}
5131    is_var_len_args = True
5132
5133
5134class ExplodeOuter(Explode):
5135    pass
5136
5137
5138class Posexplode(Explode):
5139    pass
5140
5141
5142class PosexplodeOuter(Posexplode, ExplodeOuter):
5143    pass
5144
5145
5146class Floor(Func):
5147    arg_types = {"this": True, "decimals": False}
5148
5149
5150class FromBase64(Func):
5151    pass
5152
5153
5154class ToBase64(Func):
5155    pass
5156
5157
5158class GenerateDateArray(Func):
5159    arg_types = {"start": True, "end": True, "interval": False}
5160
5161
5162class Greatest(Func):
5163    arg_types = {"this": True, "expressions": False}
5164    is_var_len_args = True
5165
5166
5167class GroupConcat(AggFunc):
5168    arg_types = {"this": True, "separator": False}
5169
5170
5171class Hex(Func):
5172    pass
5173
5174
5175class Xor(Connector, Func):
5176    arg_types = {"this": False, "expression": False, "expressions": False}
5177
5178
5179class If(Func):
5180    arg_types = {"this": True, "true": True, "false": False}
5181    _sql_names = ["IF", "IIF"]
5182
5183
5184class Nullif(Func):
5185    arg_types = {"this": True, "expression": True}
5186
5187
5188class Initcap(Func):
5189    arg_types = {"this": True, "expression": False}
5190
5191
5192class IsNan(Func):
5193    _sql_names = ["IS_NAN", "ISNAN"]
5194
5195
5196class IsInf(Func):
5197    _sql_names = ["IS_INF", "ISINF"]
5198
5199
5200class JSONPath(Expression):
5201    arg_types = {"expressions": True}
5202
5203    @property
5204    def output_name(self) -> str:
5205        last_segment = self.expressions[-1].this
5206        return last_segment if isinstance(last_segment, str) else ""
5207
5208
5209class JSONPathPart(Expression):
5210    arg_types = {}
5211
5212
5213class JSONPathFilter(JSONPathPart):
5214    arg_types = {"this": True}
5215
5216
5217class JSONPathKey(JSONPathPart):
5218    arg_types = {"this": True}
5219
5220
5221class JSONPathRecursive(JSONPathPart):
5222    arg_types = {"this": False}
5223
5224
5225class JSONPathRoot(JSONPathPart):
5226    pass
5227
5228
5229class JSONPathScript(JSONPathPart):
5230    arg_types = {"this": True}
5231
5232
5233class JSONPathSlice(JSONPathPart):
5234    arg_types = {"start": False, "end": False, "step": False}
5235
5236
5237class JSONPathSelector(JSONPathPart):
5238    arg_types = {"this": True}
5239
5240
5241class JSONPathSubscript(JSONPathPart):
5242    arg_types = {"this": True}
5243
5244
5245class JSONPathUnion(JSONPathPart):
5246    arg_types = {"expressions": True}
5247
5248
5249class JSONPathWildcard(JSONPathPart):
5250    pass
5251
5252
5253class FormatJson(Expression):
5254    pass
5255
5256
5257class JSONKeyValue(Expression):
5258    arg_types = {"this": True, "expression": True}
5259
5260
5261class JSONObject(Func):
5262    arg_types = {
5263        "expressions": False,
5264        "null_handling": False,
5265        "unique_keys": False,
5266        "return_type": False,
5267        "encoding": False,
5268    }
5269
5270
5271class JSONObjectAgg(AggFunc):
5272    arg_types = {
5273        "expressions": False,
5274        "null_handling": False,
5275        "unique_keys": False,
5276        "return_type": False,
5277        "encoding": False,
5278    }
5279
5280
5281# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_ARRAY.html
5282class JSONArray(Func):
5283    arg_types = {
5284        "expressions": True,
5285        "null_handling": False,
5286        "return_type": False,
5287        "strict": False,
5288    }
5289
5290
5291# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_ARRAYAGG.html
5292class JSONArrayAgg(Func):
5293    arg_types = {
5294        "this": True,
5295        "order": False,
5296        "null_handling": False,
5297        "return_type": False,
5298        "strict": False,
5299    }
5300
5301
5302# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_TABLE.html
5303# Note: parsing of JSON column definitions is currently incomplete.
5304class JSONColumnDef(Expression):
5305    arg_types = {"this": False, "kind": False, "path": False, "nested_schema": False}
5306
5307
5308class JSONSchema(Expression):
5309    arg_types = {"expressions": True}
5310
5311
5312# # https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_TABLE.html
5313class JSONTable(Func):
5314    arg_types = {
5315        "this": True,
5316        "schema": True,
5317        "path": False,
5318        "error_handling": False,
5319        "empty_handling": False,
5320    }
5321
5322
5323class OpenJSONColumnDef(Expression):
5324    arg_types = {"this": True, "kind": True, "path": False, "as_json": False}
5325
5326
5327class OpenJSON(Func):
5328    arg_types = {"this": True, "path": False, "expressions": False}
5329
5330
5331class JSONBContains(Binary):
5332    _sql_names = ["JSONB_CONTAINS"]
5333
5334
5335class JSONExtract(Binary, Func):
5336    arg_types = {"this": True, "expression": True, "only_json_types": False, "expressions": False}
5337    _sql_names = ["JSON_EXTRACT"]
5338    is_var_len_args = True
5339
5340    @property
5341    def output_name(self) -> str:
5342        return self.expression.output_name if not self.expressions else ""
5343
5344
5345class JSONExtractScalar(Binary, Func):
5346    arg_types = {"this": True, "expression": True, "only_json_types": False, "expressions": False}
5347    _sql_names = ["JSON_EXTRACT_SCALAR"]
5348    is_var_len_args = True
5349
5350    @property
5351    def output_name(self) -> str:
5352        return self.expression.output_name
5353
5354
5355class JSONBExtract(Binary, Func):
5356    _sql_names = ["JSONB_EXTRACT"]
5357
5358
5359class JSONBExtractScalar(Binary, Func):
5360    _sql_names = ["JSONB_EXTRACT_SCALAR"]
5361
5362
5363class JSONFormat(Func):
5364    arg_types = {"this": False, "options": False}
5365    _sql_names = ["JSON_FORMAT"]
5366
5367
5368# https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#operator_member-of
5369class JSONArrayContains(Binary, Predicate, Func):
5370    _sql_names = ["JSON_ARRAY_CONTAINS"]
5371
5372
5373class ParseJSON(Func):
5374    # BigQuery, Snowflake have PARSE_JSON, Presto has JSON_PARSE
5375    _sql_names = ["PARSE_JSON", "JSON_PARSE"]
5376    arg_types = {"this": True, "expressions": False}
5377    is_var_len_args = True
5378
5379
5380class Least(Func):
5381    arg_types = {"this": True, "expressions": False}
5382    is_var_len_args = True
5383
5384
5385class Left(Func):
5386    arg_types = {"this": True, "expression": True}
5387
5388
5389class Right(Func):
5390    arg_types = {"this": True, "expression": True}
5391
5392
5393class Length(Func):
5394    _sql_names = ["LENGTH", "LEN"]
5395
5396
5397class Levenshtein(Func):
5398    arg_types = {
5399        "this": True,
5400        "expression": False,
5401        "ins_cost": False,
5402        "del_cost": False,
5403        "sub_cost": False,
5404    }
5405
5406
5407class Ln(Func):
5408    pass
5409
5410
5411class Log(Func):
5412    arg_types = {"this": True, "expression": False}
5413
5414
5415class LogicalOr(AggFunc):
5416    _sql_names = ["LOGICAL_OR", "BOOL_OR", "BOOLOR_AGG"]
5417
5418
5419class LogicalAnd(AggFunc):
5420    _sql_names = ["LOGICAL_AND", "BOOL_AND", "BOOLAND_AGG"]
5421
5422
5423class Lower(Func):
5424    _sql_names = ["LOWER", "LCASE"]
5425
5426
5427class Map(Func):
5428    arg_types = {"keys": False, "values": False}
5429
5430    @property
5431    def keys(self) -> t.List[Expression]:
5432        keys = self.args.get("keys")
5433        return keys.expressions if keys else []
5434
5435    @property
5436    def values(self) -> t.List[Expression]:
5437        values = self.args.get("values")
5438        return values.expressions if values else []
5439
5440
5441# Represents the MAP {...} syntax in DuckDB - basically convert a struct to a MAP
5442class ToMap(Func):
5443    pass
5444
5445
5446class MapFromEntries(Func):
5447    pass
5448
5449
5450class StarMap(Func):
5451    pass
5452
5453
5454class VarMap(Func):
5455    arg_types = {"keys": True, "values": True}
5456    is_var_len_args = True
5457
5458    @property
5459    def keys(self) -> t.List[Expression]:
5460        return self.args["keys"].expressions
5461
5462    @property
5463    def values(self) -> t.List[Expression]:
5464        return self.args["values"].expressions
5465
5466
5467# https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html
5468class MatchAgainst(Func):
5469    arg_types = {"this": True, "expressions": True, "modifier": False}
5470
5471
5472class Max(AggFunc):
5473    arg_types = {"this": True, "expressions": False}
5474    is_var_len_args = True
5475
5476
5477class MD5(Func):
5478    _sql_names = ["MD5"]
5479
5480
5481# Represents the variant of the MD5 function that returns a binary value
5482class MD5Digest(Func):
5483    _sql_names = ["MD5_DIGEST"]
5484
5485
5486class Min(AggFunc):
5487    arg_types = {"this": True, "expressions": False}
5488    is_var_len_args = True
5489
5490
5491class Month(Func):
5492    pass
5493
5494
5495class AddMonths(Func):
5496    arg_types = {"this": True, "expression": True}
5497
5498
5499class Nvl2(Func):
5500    arg_types = {"this": True, "true": True, "false": False}
5501
5502
5503# https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-predict#mlpredict_function
5504class Predict(Func):
5505    arg_types = {"this": True, "expression": True, "params_struct": False}
5506
5507
5508class Pow(Binary, Func):
5509    _sql_names = ["POWER", "POW"]
5510
5511
5512class PercentileCont(AggFunc):
5513    arg_types = {"this": True, "expression": False}
5514
5515
5516class PercentileDisc(AggFunc):
5517    arg_types = {"this": True, "expression": False}
5518
5519
5520class Quantile(AggFunc):
5521    arg_types = {"this": True, "quantile": True}
5522
5523
5524class ApproxQuantile(Quantile):
5525    arg_types = {"this": True, "quantile": True, "accuracy": False, "weight": False}
5526
5527
5528class Quarter(Func):
5529    pass
5530
5531
5532class Rand(Func):
5533    _sql_names = ["RAND", "RANDOM"]
5534    arg_types = {"this": False}
5535
5536
5537class Randn(Func):
5538    arg_types = {"this": False}
5539
5540
5541class RangeN(Func):
5542    arg_types = {"this": True, "expressions": True, "each": False}
5543
5544
5545class ReadCSV(Func):
5546    _sql_names = ["READ_CSV"]
5547    is_var_len_args = True
5548    arg_types = {"this": True, "expressions": False}
5549
5550
5551class Reduce(Func):
5552    arg_types = {"this": True, "initial": True, "merge": True, "finish": False}
5553
5554
5555class RegexpExtract(Func):
5556    arg_types = {
5557        "this": True,
5558        "expression": True,
5559        "position": False,
5560        "occurrence": False,
5561        "parameters": False,
5562        "group": False,
5563    }
5564
5565
5566class RegexpReplace(Func):
5567    arg_types = {
5568        "this": True,
5569        "expression": True,
5570        "replacement": False,
5571        "position": False,
5572        "occurrence": False,
5573        "modifiers": False,
5574    }
5575
5576
5577class RegexpLike(Binary, Func):
5578    arg_types = {"this": True, "expression": True, "flag": False}
5579
5580
5581class RegexpILike(Binary, Func):
5582    arg_types = {"this": True, "expression": True, "flag": False}
5583
5584
5585# https://spark.apache.org/docs/latest/api/python/reference/pyspark.sql/api/pyspark.sql.functions.split.html
5586# limit is the number of times a pattern is applied
5587class RegexpSplit(Func):
5588    arg_types = {"this": True, "expression": True, "limit": False}
5589
5590
5591class Repeat(Func):
5592    arg_types = {"this": True, "times": True}
5593
5594
5595# https://learn.microsoft.com/en-us/sql/t-sql/functions/round-transact-sql?view=sql-server-ver16
5596# tsql third argument function == trunctaion if not 0
5597class Round(Func):
5598    arg_types = {"this": True, "decimals": False, "truncate": False}
5599
5600
5601class RowNumber(Func):
5602    arg_types: t.Dict[str, t.Any] = {}
5603
5604
5605class SafeDivide(Func):
5606    arg_types = {"this": True, "expression": True}
5607
5608
5609class SHA(Func):
5610    _sql_names = ["SHA", "SHA1"]
5611
5612
5613class SHA2(Func):
5614    _sql_names = ["SHA2"]
5615    arg_types = {"this": True, "length": False}
5616
5617
5618class Sign(Func):
5619    _sql_names = ["SIGN", "SIGNUM"]
5620
5621
5622class SortArray(Func):
5623    arg_types = {"this": True, "asc": False}
5624
5625
5626class Split(Func):
5627    arg_types = {"this": True, "expression": True, "limit": False}
5628
5629
5630# Start may be omitted in the case of postgres
5631# https://www.postgresql.org/docs/9.1/functions-string.html @ Table 9-6
5632class Substring(Func):
5633    arg_types = {"this": True, "start": False, "length": False}
5634
5635
5636class StandardHash(Func):
5637    arg_types = {"this": True, "expression": False}
5638
5639
5640class StartsWith(Func):
5641    _sql_names = ["STARTS_WITH", "STARTSWITH"]
5642    arg_types = {"this": True, "expression": True}
5643
5644
5645class StrPosition(Func):
5646    arg_types = {
5647        "this": True,
5648        "substr": True,
5649        "position": False,
5650        "instance": False,
5651    }
5652
5653
5654class StrToDate(Func):
5655    arg_types = {"this": True, "format": True}
5656
5657
5658class StrToTime(Func):
5659    arg_types = {"this": True, "format": True, "zone": False}
5660
5661
5662# Spark allows unix_timestamp()
5663# https://spark.apache.org/docs/3.1.3/api/python/reference/api/pyspark.sql.functions.unix_timestamp.html
5664class StrToUnix(Func):
5665    arg_types = {"this": False, "format": False}
5666
5667
5668# https://prestodb.io/docs/current/functions/string.html
5669# https://spark.apache.org/docs/latest/api/sql/index.html#str_to_map
5670class StrToMap(Func):
5671    arg_types = {
5672        "this": True,
5673        "pair_delim": False,
5674        "key_value_delim": False,
5675        "duplicate_resolution_callback": False,
5676    }
5677
5678
5679class NumberToStr(Func):
5680    arg_types = {"this": True, "format": True, "culture": False}
5681
5682
5683class FromBase(Func):
5684    arg_types = {"this": True, "expression": True}
5685
5686
5687class Struct(Func):
5688    arg_types = {"expressions": False}
5689    is_var_len_args = True
5690
5691
5692class StructExtract(Func):
5693    arg_types = {"this": True, "expression": True}
5694
5695
5696# https://learn.microsoft.com/en-us/sql/t-sql/functions/stuff-transact-sql?view=sql-server-ver16
5697# https://docs.snowflake.com/en/sql-reference/functions/insert
5698class Stuff(Func):
5699    _sql_names = ["STUFF", "INSERT"]
5700    arg_types = {"this": True, "start": True, "length": True, "expression": True}
5701
5702
5703class Sum(AggFunc):
5704    pass
5705
5706
5707class Sqrt(Func):
5708    pass
5709
5710
5711class Stddev(AggFunc):
5712    pass
5713
5714
5715class StddevPop(AggFunc):
5716    pass
5717
5718
5719class StddevSamp(AggFunc):
5720    pass
5721
5722
5723class TimeToStr(Func):
5724    arg_types = {"this": True, "format": True, "culture": False, "timezone": False}
5725
5726
5727class TimeToTimeStr(Func):
5728    pass
5729
5730
5731class TimeToUnix(Func):
5732    pass
5733
5734
5735class TimeStrToDate(Func):
5736    pass
5737
5738
5739class TimeStrToTime(Func):
5740    pass
5741
5742
5743class TimeStrToUnix(Func):
5744    pass
5745
5746
5747class Trim(Func):
5748    arg_types = {
5749        "this": True,
5750        "expression": False,
5751        "position": False,
5752        "collation": False,
5753    }
5754
5755
5756class TsOrDsAdd(Func, TimeUnit):
5757    # return_type is used to correctly cast the arguments of this expression when transpiling it
5758    arg_types = {"this": True, "expression": True, "unit": False, "return_type": False}
5759
5760    @property
5761    def return_type(self) -> DataType:
5762        return DataType.build(self.args.get("return_type") or DataType.Type.DATE)
5763
5764
5765class TsOrDsDiff(Func, TimeUnit):
5766    arg_types = {"this": True, "expression": True, "unit": False}
5767
5768
5769class TsOrDsToDateStr(Func):
5770    pass
5771
5772
5773class TsOrDsToDate(Func):
5774    arg_types = {"this": True, "format": False, "safe": False}
5775
5776
5777class TsOrDsToTime(Func):
5778    pass
5779
5780
5781class TsOrDsToTimestamp(Func):
5782    pass
5783
5784
5785class TsOrDiToDi(Func):
5786    pass
5787
5788
5789class Unhex(Func):
5790    pass
5791
5792
5793# https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#unix_date
5794class UnixDate(Func):
5795    pass
5796
5797
5798class UnixToStr(Func):
5799    arg_types = {"this": True, "format": False}
5800
5801
5802# https://prestodb.io/docs/current/functions/datetime.html
5803# presto has weird zone/hours/minutes
5804class UnixToTime(Func):
5805    arg_types = {
5806        "this": True,
5807        "scale": False,
5808        "zone": False,
5809        "hours": False,
5810        "minutes": False,
5811        "format": False,
5812    }
5813
5814    SECONDS = Literal.number(0)
5815    DECIS = Literal.number(1)
5816    CENTIS = Literal.number(2)
5817    MILLIS = Literal.number(3)
5818    DECIMILLIS = Literal.number(4)
5819    CENTIMILLIS = Literal.number(5)
5820    MICROS = Literal.number(6)
5821    DECIMICROS = Literal.number(7)
5822    CENTIMICROS = Literal.number(8)
5823    NANOS = Literal.number(9)
5824
5825
5826class UnixToTimeStr(Func):
5827    pass
5828
5829
5830class TimestampFromParts(Func):
5831    _sql_names = ["TIMESTAMP_FROM_PARTS", "TIMESTAMPFROMPARTS"]
5832    arg_types = {
5833        "year": True,
5834        "month": True,
5835        "day": True,
5836        "hour": True,
5837        "min": True,
5838        "sec": True,
5839        "nano": False,
5840        "zone": False,
5841        "milli": False,
5842    }
5843
5844
5845class Upper(Func):
5846    _sql_names = ["UPPER", "UCASE"]
5847
5848
5849class Corr(Binary, AggFunc):
5850    pass
5851
5852
5853class Variance(AggFunc):
5854    _sql_names = ["VARIANCE", "VARIANCE_SAMP", "VAR_SAMP"]
5855
5856
5857class VariancePop(AggFunc):
5858    _sql_names = ["VARIANCE_POP", "VAR_POP"]
5859
5860
5861class CovarSamp(Binary, AggFunc):
5862    pass
5863
5864
5865class CovarPop(Binary, AggFunc):
5866    pass
5867
5868
5869class Week(Func):
5870    arg_types = {"this": True, "mode": False}
5871
5872
5873class XMLTable(Func):
5874    arg_types = {"this": True, "passing": False, "columns": False, "by_ref": False}
5875
5876
5877class Year(Func):
5878    pass
5879
5880
5881class Use(Expression):
5882    arg_types = {"this": True, "kind": False}
5883
5884
5885class Merge(Expression):
5886    arg_types = {
5887        "this": True,
5888        "using": True,
5889        "on": True,
5890        "expressions": True,
5891        "with": False,
5892    }
5893
5894
5895class When(Func):
5896    arg_types = {"matched": True, "source": False, "condition": False, "then": True}
5897
5898
5899# https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqljnextvaluefor.html
5900# https://learn.microsoft.com/en-us/sql/t-sql/functions/next-value-for-transact-sql?view=sql-server-ver16
5901class NextValueFor(Func):
5902    arg_types = {"this": True, "order": False}
5903
5904
5905def _norm_arg(arg):
5906    return arg.lower() if type(arg) is str else arg
5907
5908
5909ALL_FUNCTIONS = subclasses(__name__, Func, (AggFunc, Anonymous, Func))
5910FUNCTION_BY_NAME = {name: func for func in ALL_FUNCTIONS for name in func.sql_names()}
5911
5912JSON_PATH_PARTS = subclasses(__name__, JSONPathPart, (JSONPathPart,))
5913
5914PERCENTILES = (PercentileCont, PercentileDisc)
5915
5916
5917# Helpers
5918@t.overload
5919def maybe_parse(
5920    sql_or_expression: ExpOrStr,
5921    *,
5922    into: t.Type[E],
5923    dialect: DialectType = None,
5924    prefix: t.Optional[str] = None,
5925    copy: bool = False,
5926    **opts,
5927) -> E: ...
5928
5929
5930@t.overload
5931def maybe_parse(
5932    sql_or_expression: str | E,
5933    *,
5934    into: t.Optional[IntoType] = None,
5935    dialect: DialectType = None,
5936    prefix: t.Optional[str] = None,
5937    copy: bool = False,
5938    **opts,
5939) -> E: ...
5940
5941
5942def maybe_parse(
5943    sql_or_expression: ExpOrStr,
5944    *,
5945    into: t.Optional[IntoType] = None,
5946    dialect: DialectType = None,
5947    prefix: t.Optional[str] = None,
5948    copy: bool = False,
5949    **opts,
5950) -> Expression:
5951    """Gracefully handle a possible string or expression.
5952
5953    Example:
5954        >>> maybe_parse("1")
5955        Literal(this=1, is_string=False)
5956        >>> maybe_parse(to_identifier("x"))
5957        Identifier(this=x, quoted=False)
5958
5959    Args:
5960        sql_or_expression: the SQL code string or an expression
5961        into: the SQLGlot Expression to parse into
5962        dialect: the dialect used to parse the input expressions (in the case that an
5963            input expression is a SQL string).
5964        prefix: a string to prefix the sql with before it gets parsed
5965            (automatically includes a space)
5966        copy: whether to copy the expression.
5967        **opts: other options to use to parse the input expressions (again, in the case
5968            that an input expression is a SQL string).
5969
5970    Returns:
5971        Expression: the parsed or given expression.
5972    """
5973    if isinstance(sql_or_expression, Expression):
5974        if copy:
5975            return sql_or_expression.copy()
5976        return sql_or_expression
5977
5978    if sql_or_expression is None:
5979        raise ParseError("SQL cannot be None")
5980
5981    import sqlglot
5982
5983    sql = str(sql_or_expression)
5984    if prefix:
5985        sql = f"{prefix} {sql}"
5986
5987    return sqlglot.parse_one(sql, read=dialect, into=into, **opts)
5988
5989
5990@t.overload
5991def maybe_copy(instance: None, copy: bool = True) -> None: ...
5992
5993
5994@t.overload
5995def maybe_copy(instance: E, copy: bool = True) -> E: ...
5996
5997
5998def maybe_copy(instance, copy=True):
5999    return instance.copy() if copy and instance else instance
6000
6001
6002def _to_s(node: t.Any, verbose: bool = False, level: int = 0) -> str:
6003    """Generate a textual representation of an Expression tree"""
6004    indent = "\n" + ("  " * (level + 1))
6005    delim = f",{indent}"
6006
6007    if isinstance(node, Expression):
6008        args = {k: v for k, v in node.args.items() if (v is not None and v != []) or verbose}
6009
6010        if (node.type or verbose) and not isinstance(node, DataType):
6011            args["_type"] = node.type
6012        if node.comments or verbose:
6013            args["_comments"] = node.comments
6014
6015        if verbose:
6016            args["_id"] = id(node)
6017
6018        # Inline leaves for a more compact representation
6019        if node.is_leaf():
6020            indent = ""
6021            delim = ", "
6022
6023        items = delim.join([f"{k}={_to_s(v, verbose, level + 1)}" for k, v in args.items()])
6024        return f"{node.__class__.__name__}({indent}{items})"
6025
6026    if isinstance(node, list):
6027        items = delim.join(_to_s(i, verbose, level + 1) for i in node)
6028        items = f"{indent}{items}" if items else ""
6029        return f"[{items}]"
6030
6031    # Indent multiline strings to match the current level
6032    return indent.join(textwrap.dedent(str(node).strip("\n")).splitlines())
6033
6034
6035def _is_wrong_expression(expression, into):
6036    return isinstance(expression, Expression) and not isinstance(expression, into)
6037
6038
6039def _apply_builder(
6040    expression,
6041    instance,
6042    arg,
6043    copy=True,
6044    prefix=None,
6045    into=None,
6046    dialect=None,
6047    into_arg="this",
6048    **opts,
6049):
6050    if _is_wrong_expression(expression, into):
6051        expression = into(**{into_arg: expression})
6052    instance = maybe_copy(instance, copy)
6053    expression = maybe_parse(
6054        sql_or_expression=expression,
6055        prefix=prefix,
6056        into=into,
6057        dialect=dialect,
6058        **opts,
6059    )
6060    instance.set(arg, expression)
6061    return instance
6062
6063
6064def _apply_child_list_builder(
6065    *expressions,
6066    instance,
6067    arg,
6068    append=True,
6069    copy=True,
6070    prefix=None,
6071    into=None,
6072    dialect=None,
6073    properties=None,
6074    **opts,
6075):
6076    instance = maybe_copy(instance, copy)
6077    parsed = []
6078    for expression in expressions:
6079        if expression is not None:
6080            if _is_wrong_expression(expression, into):
6081                expression = into(expressions=[expression])
6082
6083            expression = maybe_parse(
6084                expression,
6085                into=into,
6086                dialect=dialect,
6087                prefix=prefix,
6088                **opts,
6089            )
6090            parsed.extend(expression.expressions)
6091
6092    existing = instance.args.get(arg)
6093    if append and existing:
6094        parsed = existing.expressions + parsed
6095
6096    child = into(expressions=parsed)
6097    for k, v in (properties or {}).items():
6098        child.set(k, v)
6099    instance.set(arg, child)
6100
6101    return instance
6102
6103
6104def _apply_list_builder(
6105    *expressions,
6106    instance,
6107    arg,
6108    append=True,
6109    copy=True,
6110    prefix=None,
6111    into=None,
6112    dialect=None,
6113    **opts,
6114):
6115    inst = maybe_copy(instance, copy)
6116
6117    expressions = [
6118        maybe_parse(
6119            sql_or_expression=expression,
6120            into=into,
6121            prefix=prefix,
6122            dialect=dialect,
6123            **opts,
6124        )
6125        for expression in expressions
6126        if expression is not None
6127    ]
6128
6129    existing_expressions = inst.args.get(arg)
6130    if append and existing_expressions:
6131        expressions = existing_expressions + expressions
6132
6133    inst.set(arg, expressions)
6134    return inst
6135
6136
6137def _apply_conjunction_builder(
6138    *expressions,
6139    instance,
6140    arg,
6141    into=None,
6142    append=True,
6143    copy=True,
6144    dialect=None,
6145    **opts,
6146):
6147    expressions = [exp for exp in expressions if exp is not None and exp != ""]
6148    if not expressions:
6149        return instance
6150
6151    inst = maybe_copy(instance, copy)
6152
6153    existing = inst.args.get(arg)
6154    if append and existing is not None:
6155        expressions = [existing.this if into else existing] + list(expressions)
6156
6157    node = and_(*expressions, dialect=dialect, copy=copy, **opts)
6158
6159    inst.set(arg, into(this=node) if into else node)
6160    return inst
6161
6162
6163def _apply_cte_builder(
6164    instance: E,
6165    alias: ExpOrStr,
6166    as_: ExpOrStr,
6167    recursive: t.Optional[bool] = None,
6168    append: bool = True,
6169    dialect: DialectType = None,
6170    copy: bool = True,
6171    **opts,
6172) -> E:
6173    alias_expression = maybe_parse(alias, dialect=dialect, into=TableAlias, **opts)
6174    as_expression = maybe_parse(as_, dialect=dialect, **opts)
6175    cte = CTE(this=as_expression, alias=alias_expression)
6176    return _apply_child_list_builder(
6177        cte,
6178        instance=instance,
6179        arg="with",
6180        append=append,
6181        copy=copy,
6182        into=With,
6183        properties={"recursive": recursive or False},
6184    )
6185
6186
6187def _combine(
6188    expressions: t.Sequence[t.Optional[ExpOrStr]],
6189    operator: t.Type[Connector],
6190    dialect: DialectType = None,
6191    copy: bool = True,
6192    **opts,
6193) -> Expression:
6194    conditions = [
6195        condition(expression, dialect=dialect, copy=copy, **opts)
6196        for expression in expressions
6197        if expression is not None
6198    ]
6199
6200    this, *rest = conditions
6201    if rest:
6202        this = _wrap(this, Connector)
6203    for expression in rest:
6204        this = operator(this=this, expression=_wrap(expression, Connector))
6205
6206    return this
6207
6208
6209def _wrap(expression: E, kind: t.Type[Expression]) -> E | Paren:
6210    return Paren(this=expression) if isinstance(expression, kind) else expression
6211
6212
6213def union(
6214    left: ExpOrStr,
6215    right: ExpOrStr,
6216    distinct: bool = True,
6217    dialect: DialectType = None,
6218    copy: bool = True,
6219    **opts,
6220) -> Union:
6221    """
6222    Initializes a syntax tree from one UNION expression.
6223
6224    Example:
6225        >>> union("SELECT * FROM foo", "SELECT * FROM bla").sql()
6226        'SELECT * FROM foo UNION SELECT * FROM bla'
6227
6228    Args:
6229        left: the SQL code string corresponding to the left-hand side.
6230            If an `Expression` instance is passed, it will be used as-is.
6231        right: the SQL code string corresponding to the right-hand side.
6232            If an `Expression` instance is passed, it will be used as-is.
6233        distinct: set the DISTINCT flag if and only if this is true.
6234        dialect: the dialect used to parse the input expression.
6235        copy: whether to copy the expression.
6236        opts: other options to use to parse the input expressions.
6237
6238    Returns:
6239        The new Union instance.
6240    """
6241    left = maybe_parse(sql_or_expression=left, dialect=dialect, copy=copy, **opts)
6242    right = maybe_parse(sql_or_expression=right, dialect=dialect, copy=copy, **opts)
6243
6244    return Union(this=left, expression=right, distinct=distinct)
6245
6246
6247def intersect(
6248    left: ExpOrStr,
6249    right: ExpOrStr,
6250    distinct: bool = True,
6251    dialect: DialectType = None,
6252    copy: bool = True,
6253    **opts,
6254) -> Intersect:
6255    """
6256    Initializes a syntax tree from one INTERSECT expression.
6257
6258    Example:
6259        >>> intersect("SELECT * FROM foo", "SELECT * FROM bla").sql()
6260        'SELECT * FROM foo INTERSECT SELECT * FROM bla'
6261
6262    Args:
6263        left: the SQL code string corresponding to the left-hand side.
6264            If an `Expression` instance is passed, it will be used as-is.
6265        right: the SQL code string corresponding to the right-hand side.
6266            If an `Expression` instance is passed, it will be used as-is.
6267        distinct: set the DISTINCT flag if and only if this is true.
6268        dialect: the dialect used to parse the input expression.
6269        copy: whether to copy the expression.
6270        opts: other options to use to parse the input expressions.
6271
6272    Returns:
6273        The new Intersect instance.
6274    """
6275    left = maybe_parse(sql_or_expression=left, dialect=dialect, copy=copy, **opts)
6276    right = maybe_parse(sql_or_expression=right, dialect=dialect, copy=copy, **opts)
6277
6278    return Intersect(this=left, expression=right, distinct=distinct)
6279
6280
6281def except_(
6282    left: ExpOrStr,
6283    right: ExpOrStr,
6284    distinct: bool = True,
6285    dialect: DialectType = None,
6286    copy: bool = True,
6287    **opts,
6288) -> Except:
6289    """
6290    Initializes a syntax tree from one EXCEPT expression.
6291
6292    Example:
6293        >>> except_("SELECT * FROM foo", "SELECT * FROM bla").sql()
6294        'SELECT * FROM foo EXCEPT SELECT * FROM bla'
6295
6296    Args:
6297        left: the SQL code string corresponding to the left-hand side.
6298            If an `Expression` instance is passed, it will be used as-is.
6299        right: the SQL code string corresponding to the right-hand side.
6300            If an `Expression` instance is passed, it will be used as-is.
6301        distinct: set the DISTINCT flag if and only if this is true.
6302        dialect: the dialect used to parse the input expression.
6303        copy: whether to copy the expression.
6304        opts: other options to use to parse the input expressions.
6305
6306    Returns:
6307        The new Except instance.
6308    """
6309    left = maybe_parse(sql_or_expression=left, dialect=dialect, copy=copy, **opts)
6310    right = maybe_parse(sql_or_expression=right, dialect=dialect, copy=copy, **opts)
6311
6312    return Except(this=left, expression=right, distinct=distinct)
6313
6314
6315def select(*expressions: ExpOrStr, dialect: DialectType = None, **opts) -> Select:
6316    """
6317    Initializes a syntax tree from one or multiple SELECT expressions.
6318
6319    Example:
6320        >>> select("col1", "col2").from_("tbl").sql()
6321        'SELECT col1, col2 FROM tbl'
6322
6323    Args:
6324        *expressions: the SQL code string to parse as the expressions of a
6325            SELECT statement. If an Expression instance is passed, this is used as-is.
6326        dialect: the dialect used to parse the input expressions (in the case that an
6327            input expression is a SQL string).
6328        **opts: other options to use to parse the input expressions (again, in the case
6329            that an input expression is a SQL string).
6330
6331    Returns:
6332        Select: the syntax tree for the SELECT statement.
6333    """
6334    return Select().select(*expressions, dialect=dialect, **opts)
6335
6336
6337def from_(expression: ExpOrStr, dialect: DialectType = None, **opts) -> Select:
6338    """
6339    Initializes a syntax tree from a FROM expression.
6340
6341    Example:
6342        >>> from_("tbl").select("col1", "col2").sql()
6343        'SELECT col1, col2 FROM tbl'
6344
6345    Args:
6346        *expression: the SQL code string to parse as the FROM expressions of a
6347            SELECT statement. If an Expression instance is passed, this is used as-is.
6348        dialect: the dialect used to parse the input expression (in the case that the
6349            input expression is a SQL string).
6350        **opts: other options to use to parse the input expressions (again, in the case
6351            that the input expression is a SQL string).
6352
6353    Returns:
6354        Select: the syntax tree for the SELECT statement.
6355    """
6356    return Select().from_(expression, dialect=dialect, **opts)
6357
6358
6359def update(
6360    table: str | Table,
6361    properties: dict,
6362    where: t.Optional[ExpOrStr] = None,
6363    from_: t.Optional[ExpOrStr] = None,
6364    dialect: DialectType = None,
6365    **opts,
6366) -> Update:
6367    """
6368    Creates an update statement.
6369
6370    Example:
6371        >>> update("my_table", {"x": 1, "y": "2", "z": None}, from_="baz", where="id > 1").sql()
6372        "UPDATE my_table SET x = 1, y = '2', z = NULL FROM baz WHERE id > 1"
6373
6374    Args:
6375        *properties: dictionary of properties to set which are
6376            auto converted to sql objects eg None -> NULL
6377        where: sql conditional parsed into a WHERE statement
6378        from_: sql statement parsed into a FROM statement
6379        dialect: the dialect used to parse the input expressions.
6380        **opts: other options to use to parse the input expressions.
6381
6382    Returns:
6383        Update: the syntax tree for the UPDATE statement.
6384    """
6385    update_expr = Update(this=maybe_parse(table, into=Table, dialect=dialect))
6386    update_expr.set(
6387        "expressions",
6388        [
6389            EQ(this=maybe_parse(k, dialect=dialect, **opts), expression=convert(v))
6390            for k, v in properties.items()
6391        ],
6392    )
6393    if from_:
6394        update_expr.set(
6395            "from",
6396            maybe_parse(from_, into=From, dialect=dialect, prefix="FROM", **opts),
6397        )
6398    if isinstance(where, Condition):
6399        where = Where(this=where)
6400    if where:
6401        update_expr.set(
6402            "where",
6403            maybe_parse(where, into=Where, dialect=dialect, prefix="WHERE", **opts),
6404        )
6405    return update_expr
6406
6407
6408def delete(
6409    table: ExpOrStr,
6410    where: t.Optional[ExpOrStr] = None,
6411    returning: t.Optional[ExpOrStr] = None,
6412    dialect: DialectType = None,
6413    **opts,
6414) -> Delete:
6415    """
6416    Builds a delete statement.
6417
6418    Example:
6419        >>> delete("my_table", where="id > 1").sql()
6420        'DELETE FROM my_table WHERE id > 1'
6421
6422    Args:
6423        where: sql conditional parsed into a WHERE statement
6424        returning: sql conditional parsed into a RETURNING statement
6425        dialect: the dialect used to parse the input expressions.
6426        **opts: other options to use to parse the input expressions.
6427
6428    Returns:
6429        Delete: the syntax tree for the DELETE statement.
6430    """
6431    delete_expr = Delete().delete(table, dialect=dialect, copy=False, **opts)
6432    if where:
6433        delete_expr = delete_expr.where(where, dialect=dialect, copy=False, **opts)
6434    if returning:
6435        delete_expr = t.cast(
6436            Delete, delete_expr.returning(returning, dialect=dialect, copy=False, **opts)
6437        )
6438    return delete_expr
6439
6440
6441def insert(
6442    expression: ExpOrStr,
6443    into: ExpOrStr,
6444    columns: t.Optional[t.Sequence[str | Identifier]] = None,
6445    overwrite: t.Optional[bool] = None,
6446    returning: t.Optional[ExpOrStr] = None,
6447    dialect: DialectType = None,
6448    copy: bool = True,
6449    **opts,
6450) -> Insert:
6451    """
6452    Builds an INSERT statement.
6453
6454    Example:
6455        >>> insert("VALUES (1, 2, 3)", "tbl").sql()
6456        'INSERT INTO tbl VALUES (1, 2, 3)'
6457
6458    Args:
6459        expression: the sql string or expression of the INSERT statement
6460        into: the tbl to insert data to.
6461        columns: optionally the table's column names.
6462        overwrite: whether to INSERT OVERWRITE or not.
6463        returning: sql conditional parsed into a RETURNING statement
6464        dialect: the dialect used to parse the input expressions.
6465        copy: whether to copy the expression.
6466        **opts: other options to use to parse the input expressions.
6467
6468    Returns:
6469        Insert: the syntax tree for the INSERT statement.
6470    """
6471    expr = maybe_parse(expression, dialect=dialect, copy=copy, **opts)
6472    this: Table | Schema = maybe_parse(into, into=Table, dialect=dialect, copy=copy, **opts)
6473
6474    if columns:
6475        this = Schema(this=this, expressions=[to_identifier(c, copy=copy) for c in columns])
6476
6477    insert = Insert(this=this, expression=expr, overwrite=overwrite)
6478
6479    if returning:
6480        insert = t.cast(Insert, insert.returning(returning, dialect=dialect, copy=False, **opts))
6481
6482    return insert
6483
6484
6485def condition(
6486    expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts
6487) -> Condition:
6488    """
6489    Initialize a logical condition expression.
6490
6491    Example:
6492        >>> condition("x=1").sql()
6493        'x = 1'
6494
6495        This is helpful for composing larger logical syntax trees:
6496        >>> where = condition("x=1")
6497        >>> where = where.and_("y=1")
6498        >>> Select().from_("tbl").select("*").where(where).sql()
6499        'SELECT * FROM tbl WHERE x = 1 AND y = 1'
6500
6501    Args:
6502        *expression: the SQL code string to parse.
6503            If an Expression instance is passed, this is used as-is.
6504        dialect: the dialect used to parse the input expression (in the case that the
6505            input expression is a SQL string).
6506        copy: Whether to copy `expression` (only applies to expressions).
6507        **opts: other options to use to parse the input expressions (again, in the case
6508            that the input expression is a SQL string).
6509
6510    Returns:
6511        The new Condition instance
6512    """
6513    return maybe_parse(
6514        expression,
6515        into=Condition,
6516        dialect=dialect,
6517        copy=copy,
6518        **opts,
6519    )
6520
6521
6522def and_(
6523    *expressions: t.Optional[ExpOrStr], dialect: DialectType = None, copy: bool = True, **opts
6524) -> Condition:
6525    """
6526    Combine multiple conditions with an AND logical operator.
6527
6528    Example:
6529        >>> and_("x=1", and_("y=1", "z=1")).sql()
6530        'x = 1 AND (y = 1 AND z = 1)'
6531
6532    Args:
6533        *expressions: the SQL code strings to parse.
6534            If an Expression instance is passed, this is used as-is.
6535        dialect: the dialect used to parse the input expression.
6536        copy: whether to copy `expressions` (only applies to Expressions).
6537        **opts: other options to use to parse the input expressions.
6538
6539    Returns:
6540        The new condition
6541    """
6542    return t.cast(Condition, _combine(expressions, And, dialect, copy=copy, **opts))
6543
6544
6545def or_(
6546    *expressions: t.Optional[ExpOrStr], dialect: DialectType = None, copy: bool = True, **opts
6547) -> Condition:
6548    """
6549    Combine multiple conditions with an OR logical operator.
6550
6551    Example:
6552        >>> or_("x=1", or_("y=1", "z=1")).sql()
6553        'x = 1 OR (y = 1 OR z = 1)'
6554
6555    Args:
6556        *expressions: the SQL code strings to parse.
6557            If an Expression instance is passed, this is used as-is.
6558        dialect: the dialect used to parse the input expression.
6559        copy: whether to copy `expressions` (only applies to Expressions).
6560        **opts: other options to use to parse the input expressions.
6561
6562    Returns:
6563        The new condition
6564    """
6565    return t.cast(Condition, _combine(expressions, Or, dialect, copy=copy, **opts))
6566
6567
6568def xor(
6569    *expressions: t.Optional[ExpOrStr], dialect: DialectType = None, copy: bool = True, **opts
6570) -> Condition:
6571    """
6572    Combine multiple conditions with an XOR logical operator.
6573
6574    Example:
6575        >>> xor("x=1", xor("y=1", "z=1")).sql()
6576        'x = 1 XOR (y = 1 XOR z = 1)'
6577
6578    Args:
6579        *expressions: the SQL code strings to parse.
6580            If an Expression instance is passed, this is used as-is.
6581        dialect: the dialect used to parse the input expression.
6582        copy: whether to copy `expressions` (only applies to Expressions).
6583        **opts: other options to use to parse the input expressions.
6584
6585    Returns:
6586        The new condition
6587    """
6588    return t.cast(Condition, _combine(expressions, Xor, dialect, copy=copy, **opts))
6589
6590
6591def not_(expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts) -> Not:
6592    """
6593    Wrap a condition with a NOT operator.
6594
6595    Example:
6596        >>> not_("this_suit='black'").sql()
6597        "NOT this_suit = 'black'"
6598
6599    Args:
6600        expression: the SQL code string to parse.
6601            If an Expression instance is passed, this is used as-is.
6602        dialect: the dialect used to parse the input expression.
6603        copy: whether to copy the expression or not.
6604        **opts: other options to use to parse the input expressions.
6605
6606    Returns:
6607        The new condition.
6608    """
6609    this = condition(
6610        expression,
6611        dialect=dialect,
6612        copy=copy,
6613        **opts,
6614    )
6615    return Not(this=_wrap(this, Connector))
6616
6617
6618def paren(expression: ExpOrStr, copy: bool = True) -> Paren:
6619    """
6620    Wrap an expression in parentheses.
6621
6622    Example:
6623        >>> paren("5 + 3").sql()
6624        '(5 + 3)'
6625
6626    Args:
6627        expression: the SQL code string to parse.
6628            If an Expression instance is passed, this is used as-is.
6629        copy: whether to copy the expression or not.
6630
6631    Returns:
6632        The wrapped expression.
6633    """
6634    return Paren(this=maybe_parse(expression, copy=copy))
6635
6636
6637SAFE_IDENTIFIER_RE: t.Pattern[str] = re.compile(r"^[_a-zA-Z][\w]*$")
6638
6639
6640@t.overload
6641def to_identifier(name: None, quoted: t.Optional[bool] = None, copy: bool = True) -> None: ...
6642
6643
6644@t.overload
6645def to_identifier(
6646    name: str | Identifier, quoted: t.Optional[bool] = None, copy: bool = True
6647) -> Identifier: ...
6648
6649
6650def to_identifier(name, quoted=None, copy=True):
6651    """Builds an identifier.
6652
6653    Args:
6654        name: The name to turn into an identifier.
6655        quoted: Whether to force quote the identifier.
6656        copy: Whether to copy name if it's an Identifier.
6657
6658    Returns:
6659        The identifier ast node.
6660    """
6661
6662    if name is None:
6663        return None
6664
6665    if isinstance(name, Identifier):
6666        identifier = maybe_copy(name, copy)
6667    elif isinstance(name, str):
6668        identifier = Identifier(
6669            this=name,
6670            quoted=not SAFE_IDENTIFIER_RE.match(name) if quoted is None else quoted,
6671        )
6672    else:
6673        raise ValueError(f"Name needs to be a string or an Identifier, got: {name.__class__}")
6674    return identifier
6675
6676
6677def parse_identifier(name: str | Identifier, dialect: DialectType = None) -> Identifier:
6678    """
6679    Parses a given string into an identifier.
6680
6681    Args:
6682        name: The name to parse into an identifier.
6683        dialect: The dialect to parse against.
6684
6685    Returns:
6686        The identifier ast node.
6687    """
6688    try:
6689        expression = maybe_parse(name, dialect=dialect, into=Identifier)
6690    except ParseError:
6691        expression = to_identifier(name)
6692
6693    return expression
6694
6695
6696INTERVAL_STRING_RE = re.compile(r"\s*([0-9]+)\s*([a-zA-Z]+)\s*")
6697
6698
6699def to_interval(interval: str | Literal) -> Interval:
6700    """Builds an interval expression from a string like '1 day' or '5 months'."""
6701    if isinstance(interval, Literal):
6702        if not interval.is_string:
6703            raise ValueError("Invalid interval string.")
6704
6705        interval = interval.this
6706
6707    interval_parts = INTERVAL_STRING_RE.match(interval)  # type: ignore
6708
6709    if not interval_parts:
6710        raise ValueError("Invalid interval string.")
6711
6712    return Interval(
6713        this=Literal.string(interval_parts.group(1)),
6714        unit=Var(this=interval_parts.group(2).upper()),
6715    )
6716
6717
6718def to_table(
6719    sql_path: str | Table, dialect: DialectType = None, copy: bool = True, **kwargs
6720) -> Table:
6721    """
6722    Create a table expression from a `[catalog].[schema].[table]` sql path. Catalog and schema are optional.
6723    If a table is passed in then that table is returned.
6724
6725    Args:
6726        sql_path: a `[catalog].[schema].[table]` string.
6727        dialect: the source dialect according to which the table name will be parsed.
6728        copy: Whether to copy a table if it is passed in.
6729        kwargs: the kwargs to instantiate the resulting `Table` expression with.
6730
6731    Returns:
6732        A table expression.
6733    """
6734    if isinstance(sql_path, Table):
6735        return maybe_copy(sql_path, copy=copy)
6736
6737    table = maybe_parse(sql_path, into=Table, dialect=dialect)
6738
6739    for k, v in kwargs.items():
6740        table.set(k, v)
6741
6742    return table
6743
6744
6745def to_column(
6746    sql_path: str | Column,
6747    quoted: t.Optional[bool] = None,
6748    dialect: DialectType = None,
6749    copy: bool = True,
6750    **kwargs,
6751) -> Column:
6752    """
6753    Create a column from a `[table].[column]` sql path. Table is optional.
6754    If a column is passed in then that column is returned.
6755
6756    Args:
6757        sql_path: a `[table].[column]` string.
6758        quoted: Whether or not to force quote identifiers.
6759        dialect: the source dialect according to which the column name will be parsed.
6760        copy: Whether to copy a column if it is passed in.
6761        kwargs: the kwargs to instantiate the resulting `Column` expression with.
6762
6763    Returns:
6764        A column expression.
6765    """
6766    if isinstance(sql_path, Column):
6767        return maybe_copy(sql_path, copy=copy)
6768
6769    try:
6770        col = maybe_parse(sql_path, into=Column, dialect=dialect)
6771    except ParseError:
6772        return column(*reversed(sql_path.split(".")), quoted=quoted, **kwargs)
6773
6774    for k, v in kwargs.items():
6775        col.set(k, v)
6776
6777    if quoted:
6778        for i in col.find_all(Identifier):
6779            i.set("quoted", True)
6780
6781    return col
6782
6783
6784def alias_(
6785    expression: ExpOrStr,
6786    alias: t.Optional[str | Identifier],
6787    table: bool | t.Sequence[str | Identifier] = False,
6788    quoted: t.Optional[bool] = None,
6789    dialect: DialectType = None,
6790    copy: bool = True,
6791    **opts,
6792):
6793    """Create an Alias expression.
6794
6795    Example:
6796        >>> alias_('foo', 'bar').sql()
6797        'foo AS bar'
6798
6799        >>> alias_('(select 1, 2)', 'bar', table=['a', 'b']).sql()
6800        '(SELECT 1, 2) AS bar(a, b)'
6801
6802    Args:
6803        expression: the SQL code strings to parse.
6804            If an Expression instance is passed, this is used as-is.
6805        alias: the alias name to use. If the name has
6806            special characters it is quoted.
6807        table: Whether to create a table alias, can also be a list of columns.
6808        quoted: whether to quote the alias
6809        dialect: the dialect used to parse the input expression.
6810        copy: Whether to copy the expression.
6811        **opts: other options to use to parse the input expressions.
6812
6813    Returns:
6814        Alias: the aliased expression
6815    """
6816    exp = maybe_parse(expression, dialect=dialect, copy=copy, **opts)
6817    alias = to_identifier(alias, quoted=quoted)
6818
6819    if table:
6820        table_alias = TableAlias(this=alias)
6821        exp.set("alias", table_alias)
6822
6823        if not isinstance(table, bool):
6824            for column in table:
6825                table_alias.append("columns", to_identifier(column, quoted=quoted))
6826
6827        return exp
6828
6829    # We don't set the "alias" arg for Window expressions, because that would add an IDENTIFIER node in
6830    # the AST, representing a "named_window" [1] construct (eg. bigquery). What we want is an ALIAS node
6831    # for the complete Window expression.
6832    #
6833    # [1]: https://cloud.google.com/bigquery/docs/reference/standard-sql/window-function-calls
6834
6835    if "alias" in exp.arg_types and not isinstance(exp, Window):
6836        exp.set("alias", alias)
6837        return exp
6838    return Alias(this=exp, alias=alias)
6839
6840
6841def subquery(
6842    expression: ExpOrStr,
6843    alias: t.Optional[Identifier | str] = None,
6844    dialect: DialectType = None,
6845    **opts,
6846) -> Select:
6847    """
6848    Build a subquery expression that's selected from.
6849
6850    Example:
6851        >>> subquery('select x from tbl', 'bar').select('x').sql()
6852        'SELECT x FROM (SELECT x FROM tbl) AS bar'
6853
6854    Args:
6855        expression: the SQL code strings to parse.
6856            If an Expression instance is passed, this is used as-is.
6857        alias: the alias name to use.
6858        dialect: the dialect used to parse the input expression.
6859        **opts: other options to use to parse the input expressions.
6860
6861    Returns:
6862        A new Select instance with the subquery expression included.
6863    """
6864
6865    expression = maybe_parse(expression, dialect=dialect, **opts).subquery(alias, **opts)
6866    return Select().from_(expression, dialect=dialect, **opts)
6867
6868
6869@t.overload
6870def column(
6871    col: str | Identifier,
6872    table: t.Optional[str | Identifier] = None,
6873    db: t.Optional[str | Identifier] = None,
6874    catalog: t.Optional[str | Identifier] = None,
6875    *,
6876    fields: t.Collection[t.Union[str, Identifier]],
6877    quoted: t.Optional[bool] = None,
6878    copy: bool = True,
6879) -> Dot:
6880    pass
6881
6882
6883@t.overload
6884def column(
6885    col: str | Identifier,
6886    table: t.Optional[str | Identifier] = None,
6887    db: t.Optional[str | Identifier] = None,
6888    catalog: t.Optional[str | Identifier] = None,
6889    *,
6890    fields: Lit[None] = None,
6891    quoted: t.Optional[bool] = None,
6892    copy: bool = True,
6893) -> Column:
6894    pass
6895
6896
6897def column(
6898    col,
6899    table=None,
6900    db=None,
6901    catalog=None,
6902    *,
6903    fields=None,
6904    quoted=None,
6905    copy=True,
6906):
6907    """
6908    Build a Column.
6909
6910    Args:
6911        col: Column name.
6912        table: Table name.
6913        db: Database name.
6914        catalog: Catalog name.
6915        fields: Additional fields using dots.
6916        quoted: Whether to force quotes on the column's identifiers.
6917        copy: Whether to copy identifiers if passed in.
6918
6919    Returns:
6920        The new Column instance.
6921    """
6922    this = Column(
6923        this=to_identifier(col, quoted=quoted, copy=copy),
6924        table=to_identifier(table, quoted=quoted, copy=copy),
6925        db=to_identifier(db, quoted=quoted, copy=copy),
6926        catalog=to_identifier(catalog, quoted=quoted, copy=copy),
6927    )
6928
6929    if fields:
6930        this = Dot.build(
6931            (this, *(to_identifier(field, quoted=quoted, copy=copy) for field in fields))
6932        )
6933    return this
6934
6935
6936def cast(expression: ExpOrStr, to: DATA_TYPE, copy: bool = True, **opts) -> Cast:
6937    """Cast an expression to a data type.
6938
6939    Example:
6940        >>> cast('x + 1', 'int').sql()
6941        'CAST(x + 1 AS INT)'
6942
6943    Args:
6944        expression: The expression to cast.
6945        to: The datatype to cast to.
6946        copy: Whether to copy the supplied expressions.
6947
6948    Returns:
6949        The new Cast instance.
6950    """
6951    expr = maybe_parse(expression, copy=copy, **opts)
6952    data_type = DataType.build(to, copy=copy, **opts)
6953
6954    if expr.is_type(data_type):
6955        return expr
6956
6957    expr = Cast(this=expr, to=data_type)
6958    expr.type = data_type
6959
6960    return expr
6961
6962
6963def table_(
6964    table: Identifier | str,
6965    db: t.Optional[Identifier | str] = None,
6966    catalog: t.Optional[Identifier | str] = None,
6967    quoted: t.Optional[bool] = None,
6968    alias: t.Optional[Identifier | str] = None,
6969) -> Table:
6970    """Build a Table.
6971
6972    Args:
6973        table: Table name.
6974        db: Database name.
6975        catalog: Catalog name.
6976        quote: Whether to force quotes on the table's identifiers.
6977        alias: Table's alias.
6978
6979    Returns:
6980        The new Table instance.
6981    """
6982    return Table(
6983        this=to_identifier(table, quoted=quoted) if table else None,
6984        db=to_identifier(db, quoted=quoted) if db else None,
6985        catalog=to_identifier(catalog, quoted=quoted) if catalog else None,
6986        alias=TableAlias(this=to_identifier(alias)) if alias else None,
6987    )
6988
6989
6990def values(
6991    values: t.Iterable[t.Tuple[t.Any, ...]],
6992    alias: t.Optional[str] = None,
6993    columns: t.Optional[t.Iterable[str] | t.Dict[str, DataType]] = None,
6994) -> Values:
6995    """Build VALUES statement.
6996
6997    Example:
6998        >>> values([(1, '2')]).sql()
6999        "VALUES (1, '2')"
7000
7001    Args:
7002        values: values statements that will be converted to SQL
7003        alias: optional alias
7004        columns: Optional list of ordered column names or ordered dictionary of column names to types.
7005         If either are provided then an alias is also required.
7006
7007    Returns:
7008        Values: the Values expression object
7009    """
7010    if columns and not alias:
7011        raise ValueError("Alias is required when providing columns")
7012
7013    return Values(
7014        expressions=[convert(tup) for tup in values],
7015        alias=(
7016            TableAlias(this=to_identifier(alias), columns=[to_identifier(x) for x in columns])
7017            if columns
7018            else (TableAlias(this=to_identifier(alias)) if alias else None)
7019        ),
7020    )
7021
7022
7023def var(name: t.Optional[ExpOrStr]) -> Var:
7024    """Build a SQL variable.
7025
7026    Example:
7027        >>> repr(var('x'))
7028        'Var(this=x)'
7029
7030        >>> repr(var(column('x', table='y')))
7031        'Var(this=x)'
7032
7033    Args:
7034        name: The name of the var or an expression who's name will become the var.
7035
7036    Returns:
7037        The new variable node.
7038    """
7039    if not name:
7040        raise ValueError("Cannot convert empty name into var.")
7041
7042    if isinstance(name, Expression):
7043        name = name.name
7044    return Var(this=name)
7045
7046
7047def rename_table(
7048    old_name: str | Table,
7049    new_name: str | Table,
7050    dialect: DialectType = None,
7051) -> AlterTable:
7052    """Build ALTER TABLE... RENAME... expression
7053
7054    Args:
7055        old_name: The old name of the table
7056        new_name: The new name of the table
7057        dialect: The dialect to parse the table.
7058
7059    Returns:
7060        Alter table expression
7061    """
7062    old_table = to_table(old_name, dialect=dialect)
7063    new_table = to_table(new_name, dialect=dialect)
7064    return AlterTable(
7065        this=old_table,
7066        actions=[
7067            RenameTable(this=new_table),
7068        ],
7069    )
7070
7071
7072def rename_column(
7073    table_name: str | Table,
7074    old_column_name: str | Column,
7075    new_column_name: str | Column,
7076    exists: t.Optional[bool] = None,
7077    dialect: DialectType = None,
7078) -> AlterTable:
7079    """Build ALTER TABLE... RENAME COLUMN... expression
7080
7081    Args:
7082        table_name: Name of the table
7083        old_column: The old name of the column
7084        new_column: The new name of the column
7085        exists: Whether to add the `IF EXISTS` clause
7086        dialect: The dialect to parse the table/column.
7087
7088    Returns:
7089        Alter table expression
7090    """
7091    table = to_table(table_name, dialect=dialect)
7092    old_column = to_column(old_column_name, dialect=dialect)
7093    new_column = to_column(new_column_name, dialect=dialect)
7094    return AlterTable(
7095        this=table,
7096        actions=[
7097            RenameColumn(this=old_column, to=new_column, exists=exists),
7098        ],
7099    )
7100
7101
7102def convert(value: t.Any, copy: bool = False) -> Expression:
7103    """Convert a python value into an expression object.
7104
7105    Raises an error if a conversion is not possible.
7106
7107    Args:
7108        value: A python object.
7109        copy: Whether to copy `value` (only applies to Expressions and collections).
7110
7111    Returns:
7112        The equivalent expression object.
7113    """
7114    if isinstance(value, Expression):
7115        return maybe_copy(value, copy)
7116    if isinstance(value, str):
7117        return Literal.string(value)
7118    if isinstance(value, bool):
7119        return Boolean(this=value)
7120    if value is None or (isinstance(value, float) and math.isnan(value)):
7121        return null()
7122    if isinstance(value, numbers.Number):
7123        return Literal.number(value)
7124    if isinstance(value, bytes):
7125        return HexString(this=value.hex())
7126    if isinstance(value, datetime.datetime):
7127        datetime_literal = Literal.string(
7128            (value if value.tzinfo else value.replace(tzinfo=datetime.timezone.utc)).isoformat(
7129                sep=" "
7130            )
7131        )
7132        return TimeStrToTime(this=datetime_literal)
7133    if isinstance(value, datetime.date):
7134        date_literal = Literal.string(value.strftime("%Y-%m-%d"))
7135        return DateStrToDate(this=date_literal)
7136    if isinstance(value, tuple):
7137        if hasattr(value, "_fields"):
7138            return Struct(
7139                expressions=[
7140                    PropertyEQ(
7141                        this=to_identifier(k), expression=convert(getattr(value, k), copy=copy)
7142                    )
7143                    for k in value._fields
7144                ]
7145            )
7146        return Tuple(expressions=[convert(v, copy=copy) for v in value])
7147    if isinstance(value, list):
7148        return Array(expressions=[convert(v, copy=copy) for v in value])
7149    if isinstance(value, dict):
7150        return Map(
7151            keys=Array(expressions=[convert(k, copy=copy) for k in value]),
7152            values=Array(expressions=[convert(v, copy=copy) for v in value.values()]),
7153        )
7154    if hasattr(value, "__dict__"):
7155        return Struct(
7156            expressions=[
7157                PropertyEQ(this=to_identifier(k), expression=convert(v, copy=copy))
7158                for k, v in value.__dict__.items()
7159            ]
7160        )
7161    raise ValueError(f"Cannot convert {value}")
7162
7163
7164def replace_children(expression: Expression, fun: t.Callable, *args, **kwargs) -> None:
7165    """
7166    Replace children of an expression with the result of a lambda fun(child) -> exp.
7167    """
7168    for k, v in tuple(expression.args.items()):
7169        is_list_arg = type(v) is list
7170
7171        child_nodes = v if is_list_arg else [v]
7172        new_child_nodes = []
7173
7174        for cn in child_nodes:
7175            if isinstance(cn, Expression):
7176                for child_node in ensure_collection(fun(cn, *args, **kwargs)):
7177                    new_child_nodes.append(child_node)
7178            else:
7179                new_child_nodes.append(cn)
7180
7181        expression.set(k, new_child_nodes if is_list_arg else seq_get(new_child_nodes, 0))
7182
7183
7184def replace_tree(
7185    expression: Expression,
7186    fun: t.Callable,
7187    prune: t.Optional[t.Callable[[Expression], bool]] = None,
7188) -> Expression:
7189    """
7190    Replace an entire tree with the result of function calls on each node.
7191
7192    This will be traversed in reverse dfs, so leaves first.
7193    If new nodes are created as a result of function calls, they will also be traversed.
7194    """
7195    stack = list(expression.dfs(prune=prune))
7196
7197    while stack:
7198        node = stack.pop()
7199        new_node = fun(node)
7200
7201        if new_node is not node:
7202            node.replace(new_node)
7203
7204            if isinstance(new_node, Expression):
7205                stack.append(new_node)
7206
7207    return new_node
7208
7209
7210def column_table_names(expression: Expression, exclude: str = "") -> t.Set[str]:
7211    """
7212    Return all table names referenced through columns in an expression.
7213
7214    Example:
7215        >>> import sqlglot
7216        >>> sorted(column_table_names(sqlglot.parse_one("a.b AND c.d AND c.e")))
7217        ['a', 'c']
7218
7219    Args:
7220        expression: expression to find table names.
7221        exclude: a table name to exclude
7222
7223    Returns:
7224        A list of unique names.
7225    """
7226    return {
7227        table
7228        for table in (column.table for column in expression.find_all(Column))
7229        if table and table != exclude
7230    }
7231
7232
7233def table_name(table: Table | str, dialect: DialectType = None, identify: bool = False) -> str:
7234    """Get the full name of a table as a string.
7235
7236    Args:
7237        table: Table expression node or string.
7238        dialect: The dialect to generate the table name for.
7239        identify: Determines when an identifier should be quoted. Possible values are:
7240            False (default): Never quote, except in cases where it's mandatory by the dialect.
7241            True: Always quote.
7242
7243    Examples:
7244        >>> from sqlglot import exp, parse_one
7245        >>> table_name(parse_one("select * from a.b.c").find(exp.Table))
7246        'a.b.c'
7247
7248    Returns:
7249        The table name.
7250    """
7251
7252    table = maybe_parse(table, into=Table, dialect=dialect)
7253
7254    if not table:
7255        raise ValueError(f"Cannot parse {table}")
7256
7257    return ".".join(
7258        (
7259            part.sql(dialect=dialect, identify=True, copy=False)
7260            if identify or not SAFE_IDENTIFIER_RE.match(part.name)
7261            else part.name
7262        )
7263        for part in table.parts
7264    )
7265
7266
7267def normalize_table_name(table: str | Table, dialect: DialectType = None, copy: bool = True) -> str:
7268    """Returns a case normalized table name without quotes.
7269
7270    Args:
7271        table: the table to normalize
7272        dialect: the dialect to use for normalization rules
7273        copy: whether to copy the expression.
7274
7275    Examples:
7276        >>> normalize_table_name("`A-B`.c", dialect="bigquery")
7277        'A-B.c'
7278    """
7279    from sqlglot.optimizer.normalize_identifiers import normalize_identifiers
7280
7281    return ".".join(
7282        p.name
7283        for p in normalize_identifiers(
7284            to_table(table, dialect=dialect, copy=copy), dialect=dialect
7285        ).parts
7286    )
7287
7288
7289def replace_tables(
7290    expression: E, mapping: t.Dict[str, str], dialect: DialectType = None, copy: bool = True
7291) -> E:
7292    """Replace all tables in expression according to the mapping.
7293
7294    Args:
7295        expression: expression node to be transformed and replaced.
7296        mapping: mapping of table names.
7297        dialect: the dialect of the mapping table
7298        copy: whether to copy the expression.
7299
7300    Examples:
7301        >>> from sqlglot import exp, parse_one
7302        >>> replace_tables(parse_one("select * from a.b"), {"a.b": "c"}).sql()
7303        'SELECT * FROM c /* a.b */'
7304
7305    Returns:
7306        The mapped expression.
7307    """
7308
7309    mapping = {normalize_table_name(k, dialect=dialect): v for k, v in mapping.items()}
7310
7311    def _replace_tables(node: Expression) -> Expression:
7312        if isinstance(node, Table):
7313            original = normalize_table_name(node, dialect=dialect)
7314            new_name = mapping.get(original)
7315
7316            if new_name:
7317                table = to_table(
7318                    new_name,
7319                    **{k: v for k, v in node.args.items() if k not in TABLE_PARTS},
7320                    dialect=dialect,
7321                )
7322                table.add_comments([original])
7323                return table
7324        return node
7325
7326    return expression.transform(_replace_tables, copy=copy)  # type: ignore
7327
7328
7329def replace_placeholders(expression: Expression, *args, **kwargs) -> Expression:
7330    """Replace placeholders in an expression.
7331
7332    Args:
7333        expression: expression node to be transformed and replaced.
7334        args: positional names that will substitute unnamed placeholders in the given order.
7335        kwargs: keyword arguments that will substitute named placeholders.
7336
7337    Examples:
7338        >>> from sqlglot import exp, parse_one
7339        >>> replace_placeholders(
7340        ...     parse_one("select * from :tbl where ? = ?"),
7341        ...     exp.to_identifier("str_col"), "b", tbl=exp.to_identifier("foo")
7342        ... ).sql()
7343        "SELECT * FROM foo WHERE str_col = 'b'"
7344
7345    Returns:
7346        The mapped expression.
7347    """
7348
7349    def _replace_placeholders(node: Expression, args, **kwargs) -> Expression:
7350        if isinstance(node, Placeholder):
7351            if node.this:
7352                new_name = kwargs.get(node.this)
7353                if new_name is not None:
7354                    return convert(new_name)
7355            else:
7356                try:
7357                    return convert(next(args))
7358                except StopIteration:
7359                    pass
7360        return node
7361
7362    return expression.transform(_replace_placeholders, iter(args), **kwargs)
7363
7364
7365def expand(
7366    expression: Expression,
7367    sources: t.Dict[str, Query],
7368    dialect: DialectType = None,
7369    copy: bool = True,
7370) -> Expression:
7371    """Transforms an expression by expanding all referenced sources into subqueries.
7372
7373    Examples:
7374        >>> from sqlglot import parse_one
7375        >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y")}).sql()
7376        'SELECT * FROM (SELECT * FROM y) AS z /* source: x */'
7377
7378        >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y"), "y": parse_one("select * from z")}).sql()
7379        'SELECT * FROM (SELECT * FROM (SELECT * FROM z) AS y /* source: y */) AS z /* source: x */'
7380
7381    Args:
7382        expression: The expression to expand.
7383        sources: A dictionary of name to Queries.
7384        dialect: The dialect of the sources dict.
7385        copy: Whether to copy the expression during transformation. Defaults to True.
7386
7387    Returns:
7388        The transformed expression.
7389    """
7390    sources = {normalize_table_name(k, dialect=dialect): v for k, v in sources.items()}
7391
7392    def _expand(node: Expression):
7393        if isinstance(node, Table):
7394            name = normalize_table_name(node, dialect=dialect)
7395            source = sources.get(name)
7396            if source:
7397                subquery = source.subquery(node.alias or name)
7398                subquery.comments = [f"source: {name}"]
7399                return subquery.transform(_expand, copy=False)
7400        return node
7401
7402    return expression.transform(_expand, copy=copy)
7403
7404
7405def func(name: str, *args, copy: bool = True, dialect: DialectType = None, **kwargs) -> Func:
7406    """
7407    Returns a Func expression.
7408
7409    Examples:
7410        >>> func("abs", 5).sql()
7411        'ABS(5)'
7412
7413        >>> func("cast", this=5, to=DataType.build("DOUBLE")).sql()
7414        'CAST(5 AS DOUBLE)'
7415
7416    Args:
7417        name: the name of the function to build.
7418        args: the args used to instantiate the function of interest.
7419        copy: whether to copy the argument expressions.
7420        dialect: the source dialect.
7421        kwargs: the kwargs used to instantiate the function of interest.
7422
7423    Note:
7424        The arguments `args` and `kwargs` are mutually exclusive.
7425
7426    Returns:
7427        An instance of the function of interest, or an anonymous function, if `name` doesn't
7428        correspond to an existing `sqlglot.expressions.Func` class.
7429    """
7430    if args and kwargs:
7431        raise ValueError("Can't use both args and kwargs to instantiate a function.")
7432
7433    from sqlglot.dialects.dialect import Dialect
7434
7435    dialect = Dialect.get_or_raise(dialect)
7436
7437    converted: t.List[Expression] = [maybe_parse(arg, dialect=dialect, copy=copy) for arg in args]
7438    kwargs = {key: maybe_parse(value, dialect=dialect, copy=copy) for key, value in kwargs.items()}
7439
7440    constructor = dialect.parser_class.FUNCTIONS.get(name.upper())
7441    if constructor:
7442        if converted:
7443            if "dialect" in constructor.__code__.co_varnames:
7444                function = constructor(converted, dialect=dialect)
7445            else:
7446                function = constructor(converted)
7447        elif constructor.__name__ == "from_arg_list":
7448            function = constructor.__self__(**kwargs)  # type: ignore
7449        else:
7450            constructor = FUNCTION_BY_NAME.get(name.upper())
7451            if constructor:
7452                function = constructor(**kwargs)
7453            else:
7454                raise ValueError(
7455                    f"Unable to convert '{name}' into a Func. Either manually construct "
7456                    "the Func expression of interest or parse the function call."
7457                )
7458    else:
7459        kwargs = kwargs or {"expressions": converted}
7460        function = Anonymous(this=name, **kwargs)
7461
7462    for error_message in function.error_messages(converted):
7463        raise ValueError(error_message)
7464
7465    return function
7466
7467
7468def case(
7469    expression: t.Optional[ExpOrStr] = None,
7470    **opts,
7471) -> Case:
7472    """
7473    Initialize a CASE statement.
7474
7475    Example:
7476        case().when("a = 1", "foo").else_("bar")
7477
7478    Args:
7479        expression: Optionally, the input expression (not all dialects support this)
7480        **opts: Extra keyword arguments for parsing `expression`
7481    """
7482    if expression is not None:
7483        this = maybe_parse(expression, **opts)
7484    else:
7485        this = None
7486    return Case(this=this, ifs=[])
7487
7488
7489def array(
7490    *expressions: ExpOrStr, copy: bool = True, dialect: DialectType = None, **kwargs
7491) -> Array:
7492    """
7493    Returns an array.
7494
7495    Examples:
7496        >>> array(1, 'x').sql()
7497        'ARRAY(1, x)'
7498
7499    Args:
7500        expressions: the expressions to add to the array.
7501        copy: whether to copy the argument expressions.
7502        dialect: the source dialect.
7503        kwargs: the kwargs used to instantiate the function of interest.
7504
7505    Returns:
7506        An array expression.
7507    """
7508    return Array(
7509        expressions=[
7510            maybe_parse(expression, copy=copy, dialect=dialect, **kwargs)
7511            for expression in expressions
7512        ]
7513    )
7514
7515
7516def tuple_(
7517    *expressions: ExpOrStr, copy: bool = True, dialect: DialectType = None, **kwargs
7518) -> Tuple:
7519    """
7520    Returns an tuple.
7521
7522    Examples:
7523        >>> tuple_(1, 'x').sql()
7524        '(1, x)'
7525
7526    Args:
7527        expressions: the expressions to add to the tuple.
7528        copy: whether to copy the argument expressions.
7529        dialect: the source dialect.
7530        kwargs: the kwargs used to instantiate the function of interest.
7531
7532    Returns:
7533        A tuple expression.
7534    """
7535    return Tuple(
7536        expressions=[
7537            maybe_parse(expression, copy=copy, dialect=dialect, **kwargs)
7538            for expression in expressions
7539        ]
7540    )
7541
7542
7543def true() -> Boolean:
7544    """
7545    Returns a true Boolean expression.
7546    """
7547    return Boolean(this=True)
7548
7549
7550def false() -> Boolean:
7551    """
7552    Returns a false Boolean expression.
7553    """
7554    return Boolean(this=False)
7555
7556
7557def null() -> Null:
7558    """
7559    Returns a Null expression.
7560    """
7561    return Null()
7562
7563
7564NONNULL_CONSTANTS = (
7565    Literal,
7566    Boolean,
7567)
7568
7569CONSTANTS = (
7570    Literal,
7571    Boolean,
7572    Null,
7573)